{"id":615,"date":"2014-10-08T14:34:40","date_gmt":"2014-10-08T13:34:40","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=615"},"modified":"2014-10-08T15:12:31","modified_gmt":"2014-10-08T14:12:31","slug":"db2-virtual-storage-or-database-resource-is-not-available-error","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=615","title":{"rendered":"DB2 – „Virtual storage or database resource is not available“ error"},"content":{"rendered":"

Default DB2 DB and DBM variables are set pretty well and logical. The only manual adjusting can occur when you’re trying to overload transactions a bit. For example by copying tables.<\/p>\n

<\/p>\n

Task<\/strong> = Copying table (10 milions of records) within same schema (in our case ADDRESS -> ADDRESSX).<\/p>\n

We can use some GUI to fasten our operation or just do a copy of structure + data:<\/p>\n

CREATE TABLE DB2INST1.ADDRESSX LIKE DB2INST.ADDRESS;<\/span><\/p>\n

INSERT INTO DB2INST1.ADDRESSX SELECT * FROM DB2INST1.ADDRESS;<\/span><\/p>\n

In optimal case, that’s all (and of course working in default settings for smaller tables).<\/p>\n

Solution 1<\/strong> called „Hooray, here we go“<\/p>\n

—<\/p>\n

After few seconds this approach is stopped by error message (RazorSQL environment):<\/p>\n

\"db2-964\"<\/a><\/p>\n

Solution 1 final standing „Oh my, DB2 sucks, that\u00b4s terrible!“<\/p>\n

 <\/p>\n

Solution 2<\/strong> called „I can read and think a little bit“<\/p>\n

—<\/p>\n

[db2inst1@rhel65db2105 j]$ db2 ? 57011<\/strong><\/span><\/span><\/p>\n

SQLSTATE 57011: Virtual storage or database resource is not available.<\/span>
\nOk, still can be anything, let’s try most talkative command and his output \ud83d\ude42<\/p>\n

[db2inst1@rhel65db2105 j]$ db2 ? sql-964<\/strong><\/span><\/span><\/p>\n

SQL0964C\u00a0 The transaction log for the database is full.<\/span><\/p>\n

Explanation: <\/span><\/p>\n

All space in the transaction log is being used.<\/span><\/p>\n

If a circular log with secondary log files is being used, an attempt has<\/span>
\nbeen made to allocate and use them. When the file system has no more<\/span>
\nspace, secondary logs cannot be used.<\/span><\/p>\n

If an archive log is used, then the file system has not provided space<\/span>
\nto contain a new log file.<\/span><\/p>\n

This message can also be returned when the database has used almost all<\/span>
\nof the possible log sequence numbers. The database manager identifies<\/span>
\ndatabase log records using a unique identifier, called a log sequence<\/span>
\nnumber (LSN). When transactions cause database logs to be written, the<\/span>
\ndatabase uses new LSN values.<\/span><\/p>\n

The application cannot perform any transactions that cause the database<\/span>
\nto write log records.<\/span><\/p>\n

The statement cannot be processed.<\/span><\/p>\n

User response: <\/span><\/p>\n

Execute a COMMIT or ROLLBACK on receipt of this message (SQLCODE) or<\/span>
\nretry the operation.<\/span><\/p>\n

If the database is being updated by concurrent applications, retry the<\/span>
\noperation. Log space may be freed up when another application finishes a<\/span>
\ntransaction.<\/span><\/p>\n

Issue more frequent commit operations. If your transactions are not<\/span>
\ncommitted, log space may be freed up when the transactions are<\/span>
\ncommitted. When designing an application, consider when to commit the<\/span>
\nupdate transactions to prevent a log full condition.<\/span><\/p>\n

If deadlocks are occurring, check for them more frequently. This can be<\/span>
\ndone by decreasing the database configuration parameter DLCHKTIME. This<\/span>
\nwill cause deadlocks to be detected and resolved sooner (by ROLLBACK)<\/span>
\nwhich will then free log space.<\/span><\/p>\n

If the condition occurs often, increase the database configuration<\/span>
\nparameter to allow a larger log file. A larger log file requires more<\/span>
\nspace but reduces the need for applications to retry the operation.<\/span>
\nTransaction configuration parameters which may need to be adjusted are<\/span>
\nLOGFILSIZ, LOGPRIMARY, LOGSECOND. For more information about the<\/span>
\ntransaction log search the DB2 Information Center using phrases such as<\/span>
\n„transaction log“.<\/span><\/p>\n

If installing the sample database, drop it and install the sample<\/span>
\ndatabase again.<\/span><\/p>\n

If this message was returned because the database used nearly all of the<\/span>
\npossible LSN values, you can reset the LSN values to zero by doing the<\/span>
\nfollowing:<\/span><\/p>\n

1. unload all data from the database<\/span>
\n2. drop and recreate the database<\/span>
\n3. load all the data<\/span><\/p>\n

sqlcode: -964<\/span><\/p>\n

sqlstate: 57011<\/span><\/p>\n

That‘ s something i really DO LIKE on IBM products – documentation and error codes \ud83d\ude42 Just first sentence was enough to identify issue: „All space in the transaction log is being used.<\/span>„.<\/p>\n

Simply – copying table command serves well for suffocating transaction logs. If you don’t know what transaction log is, please look at very old but still active! article<\/a>.<\/p>\n

Where can I set higher values for „something“ related? Ok, you have to adjust few database configuration values (it’s strictly logical that instance (DBM) is above these database transaction problems). If you are not familiar with parameters name, you can list everything „LOG“ related:<\/p>\n

[db2inst1@rhel65db2105 ~]$ db2 get db cfg for sakila | grep '(LOG'<\/code><\/strong><\/span><\/span>
\n\u00a0Log buffer size (4KB)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGBUFSZ) = 2150<\/span>
\n\u00a0Log file size (4KB)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGFILSIZ) = 1024<\/span><\/strong><\/span>
\n \u00a0Number of primary log files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGPRIMARY) = 13<\/span><\/strong><\/span>
\n \u00a0Number of secondary log files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGSECOND) = 12<\/span><\/strong><\/span>
\n\u00a0First log archive method\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGARCHMETH1) = OFF<\/span>
\n\u00a0Archive compression for logarchmeth1\u00a0\u00a0\u00a0 (LOGARCHCOMPR1) = OFF<\/span>
\n\u00a0Options for logarchmeth1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGARCHOPT1) = <\/span>
\n\u00a0Second log archive method\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGARCHMETH2) = OFF<\/span>
\n\u00a0Archive compression for logarchmeth2\u00a0\u00a0\u00a0 (LOGARCHCOMPR2) = OFF<\/span>
\n\u00a0Options for logarchmeth2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGARCHOPT2) = <\/span>
\n\u00a0Log pages during index build\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOGINDEXBUILD) = OFF<\/span>
\n\u00a0Log DDL Statements\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOG_DDL_STMTS) = NO<\/span>
\n\u00a0Log Application Information\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (LOG_APPL_INFO) = NO<\/span><\/p>\n

You can spy through documentation to find which parameters are related to this issue. I can promise that you can focus just for these highlighted „big three“ \ud83d\ude42<\/p>\n

If you have good hardware (including free disk space), don’t waste your time with big math and just raise values:<\/p>\n

[db2inst1@rhel65db2105 ~]$ db2 update database configuration for sakila using LOGFILSIZ 50000;<\/strong><\/span><\/span>
\nDB20000I\u00a0 The UPDATE DATABASE CONFIGURATION command completed successfully.<\/span>
\n[db2inst1@rhel65db2105 ~]$ db2 update database configuration for sakila using LOGPRIMARY 40;<\/strong><\/span><\/span>
\nDB20000I\u00a0 The UPDATE DATABASE CONFIGURATION command completed successfully.<\/span><\/p>\n

[db2inst1@rhel65db2105 ~]$ db2stop force<\/strong><\/span><\/span>
\n10\/08\/2014 13:02:27\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0 0\u00a0\u00a0 SQL1064N\u00a0 DB2STOP processing was successful.<\/span>
\nSQL1064N\u00a0 DB2STOP processing was successful.<\/span><\/p>\n

After db2start<\/span> it’s counting with new values. I have performed tables copying and now I can check how many log file I have (remember 13 files as original value?).<\/p>\n

[db2inst1@rhel65db2105 LOGSTREAM0000]$ pwd<\/strong><\/span><\/span>
\n\/sak\/db2inst1\/NODE0000\/SQL00001\/LOGSTREAM0000<\/span>
\n[db2inst1@rhel65db2105 LOGSTREAM0000]$ ll<\/strong><\/span><\/span>
\ntotal 102600<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000000.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000001.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000002.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000003.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000004.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000005.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000006.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000007.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000008.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000009.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000010.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000011.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000012.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000013.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000014.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000015.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000016.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000017.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000018.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000019.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000020.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000021.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000022.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:06 S0000023.LOG<\/span>
\n-rw——- 1 db2inst1 db2iadm1 4202496 Oct\u00a0 8 14:29 S0000024.LOG<\/span><\/p>\n

Size around 4MB, 25 files.<\/p>\n

Solution 2 final standing „Interesting!“<\/p>\n

 <\/p>\n

Best practice = study DB2 parameters precisely, it will save you your time + nerves \ud83d\ude42<\/p>\n

 <\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

Default DB2 DB and DBM variables are set pretty well and logical. The only manual adjusting can occur when you’re trying to overload transactions a bit. For example by copying tables.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/615"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=615"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/615\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=615"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}