- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- DB2 Transaction log Problem after set db manuell t...
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
DB2 Transaction log Problem after set db manuell to Rollforward
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 08-11-2014 9:02 AM
Hello,
I have a problem when set the db2 manuell in rollforward mode. After I set the command db2rfpen ON E35 my database (E35) is in rollforward mode.
Now I can see with command "db2 rollforward db E35 query status" the Last committed transaction
but the timestamp of the Last committed transaction is 2106-02-07-06.28.15.000000 UTC
but we do not have year 2106!!
did someone have similar issue or a solution for my problem??
Is it possible to manuell update the table via SQL? But I dont know which table i should update..
Thanks in advanced.
Robin
- SAP Managed Tags:
- IBM Db2 for Linux, UNIX, and Windows,
- SAP NetWeaver
Accepted Solutions (0)
Answers (3)
Answers (3)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hello Robin,
The issue seems to relate to the tool 'db2rfpen'. There is an open APAR IT04943 for V10.1.
For 10.5, it is to be identified.
Kind regards
Maria
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Robin ,
**********************************
db2 rollforward db E35 query status using local time
time is
Last committed transaction = 1970-01-01-02.00.00.000000 Local
If I select "db2 rollforward db E35 query status using UTC time" time is
Last committed transaction = 2106-02-07-06.28.15.000000 UTC
**********************************
Could you please follow below step and let me know how it proceed . It is something differnet than above steps . but we use same steps and restore Database propoerly .
i) db2 rollforward db E35 query status
it will return same output like Last committed transaction = 1970-01-01-02.00.00.000000 Local
then
ii) db2 rollforward db E35 to 1970-01-01-00.00.01.000000
it will give error like
SQL1275N The stoptime passed to roll-forward must be greater than or equal to
"< timestamp > UTC", because database
Please let me know what is timestamp in error , is it again 2106 year or 2014 ? if it is 2014 then you can proceed with timestamp and rollowrd complete.
Thanks
Manas behra
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hello Manas,
thanks for your help!
there in the error "to end of logs". see my output
srvnetapp07:db2e35 111> db2 rollforward db E35 query status
Rollforward Status
Input database alias = E35
Number of members have returned status = 1
Member ID = 0
Rollforward status = DB pending
Next log file to be read = S0000088.LOG
Log files processed = -
Last committed transaction = 2106-02-07-06.28.15.000000 UTC
srvnetapp07:db2e35 112> db2 rollforward db E35 query status using local time
Rollforward Status
Input database alias = E35
Number of members have returned status = 1
Member ID = 0
Rollforward status = DB pending
Next log file to be read = S0000088.LOG
Log files processed = -
Last committed transaction = 1970-01-01-02.00.00.000000 Local
srvnetapp07:db2e35 113> db2 rollforward db E35 to 1970-01-01-00.00.01.000000
SQL1275N The stop time passed to the rollforward utility must be greater than
or equal to timestamp "END OF LOGS", because database "E35" on nodes "0"
contains information later than the specified time.
regards,
Robin
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Robin,
I am having the same issue. I restored database from offline backup and manually changed db state to rollforward pending and unable to apply logs.
[db2ecy@icux1925-db1 LOGSTREAM0000]$ db2 rollforward database ecy query status
Rollforward Status
Input database alias = ecy
Number of members have returned status = 1
Member ID = 0
Rollforward status = DB pending
Next log file to be read = S0081040.LOG
Log files processed = -
Last committed transaction = 2106-02-07-06.28.15.000000 UTC
[db2ecy@icux1925-db1 LOGSTREAM0000]$ db2 'rollforward database ECY to 2015-04-09-05.35.00.0000 USING LOCAL TIME and complete overflow log path (/backup/db2logs-nonreplicated/ECY/db2ecy/ECY/NODE0000/LOGSTREAM0000/C0000013/save/)'
SQL1275N The stop time passed to the rollforward utility must be greater than
or equal to timestamp "END OF LOGS", because database "ECY" on nodes "0"
contains information later than the specified time.
[db2ecy@icux1925-db1 LOGSTREAM0000]$
Did you find solution to your problme >
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Robin,
Have you checked the timezone on your server ?
Please share details on timezone settings on the server.
Regards,
Deepak Kori
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Deepak,
yes the time in linux OS is the actual time and when I select the DB time with SQL Statment "SELECT current timestamp FROM sysibm.sysdummy1"
the time of DB is also the correct one: 2014-08-11-10.39.21.440504
If I when DB is in Rollforwad mode command -> db2 rollforward db E35 query status using local time
time is
Last committed transaction = 1970-01-01-02.00.00.000000 Local
If I select "db2 rollforward db E35 query status using UTC time" time is
Last committed transaction = 2106-02-07-06.28.15.000000 UTC
thats very strange i dont know what to do
thanks for your help
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi,
I would like to test something and so I performe follogwing steps.
1. performe command "db2 archive log for database E35" five time because I have 5 primary archivlogs and would like to commit all transactions and write to archivlog
2. set DB in write suspend mode "db2 set write suspend for database"
3. Take a VM Ware Snapshot ( not an Hardware snapshot). I set DB in write suspend mode because i want that DB is consistent
4. set DB to write resume
5. Take a change in SAP and write down to Archivlog
performe command "db2 archive log for database E35" five time because I have 5 primary archivlogs and would like to commit all transactions and write to archivlog
6. Backup of Archivlogs
7. Restore to VM Ware Snapshot
8. copy Archivlogs to Filesystem to rollforward them in DB
9. set DB in Rollforward mode
10. Rollforward archivlogs.
But this is not possible because after i set DB in Rollforward mode the timestamp of last committed Transaction is strange.
The same Problem is when i Restore an offline Backup and then say "db2 rollforward db E35 complete" but after that I would like to restore archivlogs and set db manuell to rollforward mode.
Or other question. Is it possible to set DB manuell in Backup mode like Oracle so that i have a consistent DB at Snapshot?
regards
Robin
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi,
following output of db2 get snapshot for all databases because db2 get snapshot for database E35 does not work..
there I think the timestamp is also ok. Thanks for your help!
regards
Robin
Database Snapshot
Database name = E35
Database path = /db2/E35/db2e35/NODE0000/SQL00001/MEMBER0000/
Input database alias =
Database status = Active
Catalog database partition number = 0
Catalog network node name = srvnetapp07
Operating system running at database server= LINUXAMD64
Location of the database = Local
First database connect timestamp = 08/11/2014 12:16:58.594371
Last reset timestamp =
Last backup timestamp = 08/08/2014 09:57:42.000000
Snapshot timestamp = 08/11/2014 12:17:46.494342
Number of automatic storage paths = 4
Automatic storage path = /db2/E35/sapdata1
Node number = 0
State = In Use
File system ID = 64782
Storage path free space (bytes) = 23376351232
File system used space (bytes) = 3045990400
File system total space (bytes) = 26422341632
Automatic storage path = /db2/E35/sapdata2
Node number = 0
State = In Use
File system ID = 64783
Storage path free space (bytes) = 23376351232
File system used space (bytes) = 3045990400
File system total space (bytes) = 26422341632
Automatic storage path = /db2/E35/sapdata3
Node number = 0
State = In Use
File system ID = 64784
Storage path free space (bytes) = 23376351232
File system used space (bytes) = 3045990400
File system total space (bytes) = 26422341632
Automatic storage path = /db2/E35/sapdata4
Node number = 0
State = In Use
File system ID = 64785
Storage path free space (bytes) = 23376351232
File system used space (bytes) = 3045990400
File system total space (bytes) = 26422341632
High water mark for connections = 13
Application connects = 9
Secondary connects total = 11
Applications connected currently = 1
Appls. executing in db manager currently = 0
Agents associated with applications = 11
Maximum agents associated with applications= 13
Maximum coordinating agents = 13
Number of Threshold Violations = 0
Locks held currently = 29
Lock waits = 2
Time database waited on locks (ms) = 56
Lock list memory in use (Bytes) = 77568
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 6
Shared Sort heap high water mark = 245
Post threshold sorts (shared memory) = 0
Total sorts = 2
Total sort time (ms) = 2
Sort overflows = 0
Active sorts = 0
Buffer pool data logical reads = 68507
Buffer pool data physical reads = 1312
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 899
Buffer pool data writes = 0
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 52871
Buffer pool index physical reads = 449
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Buffer pool xda writes = 0
Asynchronous pool xda page reads = 0
Asynchronous pool xda page writes = 0
Total buffer pool read time (milliseconds) = 4912
Total buffer pool write time (milliseconds)= 0
Total elapsed asynchronous read time = 3219
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 454
Asynchronous index read requests = 0
Asynchronous xda read requests = 0
No victim buffers available = 0
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 0
Dirty page threshold cleaner triggers = 0
Time waited for prefetch (ms) = 982
Unread prefetch pages = 0
Direct reads = 986
Direct writes = 2
Direct read requests = 77
Direct write requests = 1
Direct reads elapsed time (ms) = 243
Direct write elapsed time (ms) = 0
Database files closed = 0
Host execution elapsed time = 5.785568
Commit statements attempted = 16
Rollback statements attempted = 2
Dynamic statements attempted = 6950
Static statements attempted = 23
Failed statement operations = 0
Select SQL statements executed = 6936
Xquery statements executed = 0
Update/Insert/Delete statements executed = 6
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 21
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Number of MDC table blocks pending cleanup = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 13838
Rows selected = 13897
Rows read = 141752
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 333797877
Log space used by the database (Bytes) = 26523
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 37070
Secondary logs allocated currently = 0
Log pages read = 0
Log read time (sec.ns) = 0.000000000
Log pages written = 13
Log write time (sec.ns) = 0.006250000
Number write log IOs = 9
Number read log IOs = 0
Number partial page log IOs = 7
Number log buffer full = 0
Log data found in buffer = 0
Log to be redone for recovery (Bytes) = 26523
Log accounted for by dirty pages (Bytes) = 26523
Node number = 0
File number of first active log = 90
File number of last active log = 94
File number of current active log = 90
File number of log being archived = Not applicable
Package cache lookups = 6955
Package cache inserts = 20
Package cache overflows = 0
Package cache high water mark (Bytes) = 1026593
Application section lookups = 20824
Application section inserts = 29
Catalog cache lookups = 740
Catalog cache inserts = 55
Catalog cache overflows = 0
Catalog cache high water mark = 429098
Catalog cache statistics size = 0
Workspace Information
Number of hash joins = 4
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Post threshold hash joins (shared memory) = 0
Active hash joins = 0
Number of OLAP functions = 0
Number of OLAP function overflows = 0
Active OLAP functions = 0
Statistic fabrications = 0
Synchronous runstats = 0
Asynchronous runstats = 0
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time (milliseconds) = 0
Memory usage for database:
Node number = 0
Memory Pool Type = Backup/Restore/Util Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 204800000
Node number = 0
Memory Pool Type = Package Cache Heap
Current size (bytes) = 1179648
High water mark (bytes) = 1179648
Configured size (bytes) = 472317952
Node number = 0
Memory Pool Type = Other Memory
Current size (bytes) = 196608
High water mark (bytes) = 196608
Configured size (bytes) = 20971520
Node number = 0
Memory Pool Type = Catalog Cache Heap
Current size (bytes) = 524288
High water mark (bytes) = 524288
Configured size (bytes) = 10485760
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = 1
Current size (bytes) = 494010368
High water mark (bytes) = 494010368
Configured size (bytes) = 494010368
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 32k buffer pool
Current size (bytes) = 1835008
High water mark (bytes) = 1835008
Configured size (bytes) = 1835008
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 16k buffer pool
Current size (bytes) = 1572864
High water mark (bytes) = 1572864
Configured size (bytes) = 1572864
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 8k buffer pool
Current size (bytes) = 1441792
High water mark (bytes) = 1441792
Configured size (bytes) = 1441792
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 4k buffer pool
Current size (bytes) = 1376256
High water mark (bytes) = 1376256
Configured size (bytes) = 1376256
Node number = 0
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 2359296
High water mark (bytes) = 3932160
Configured size (bytes) = 40435712
Node number = 0
Memory Pool Type = Lock Manager Heap
Current size (bytes) = 159645696
High water mark (bytes) = 159645696
Configured size (bytes) = 159711232
Node number = 0
Memory Pool Type = Database Heap
Current size (bytes) = 87687168
High water mark (bytes) = 87687168
Configured size (bytes) = 119472128
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 107
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 106
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 105
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 104
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 103
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 102
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 101
Current size (bytes) = 196608
High water mark (bytes) = 196608
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 100
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 99
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 98
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 97
Current size (bytes) = 131072
High water mark (bytes) = 131072
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Applications Shared Heap
Current size (bytes) = 458752
High water mark (bytes) = 458752
Configured size (bytes) = 81920000
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Robin,
It looks to be bug fixed with APAR LI72673.
Refer link below for details on the bug fix http://www-01.ibm.com/support/docview.wss?uid=swg1LI72673
Regards,
Deepak Kori
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
| User | Count |
|---|---|
| 78 | |
| 9 | |
| 9 | |
| 7 | |
| 7 | |
| 6 | |
| 6 | |
| 5 | |
| 5 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.