Think Out of the Big Box

A bank customer of us was requesting helps to solve a sudden problem dealing with their IBM DWE server. Apparently from certain date onwards, all the processes executed within the DWE console were failed as shown in the following figure:



Interesting because there are few applications running in DWE and some of them already run for more then 2 years without any problems and now all of them failed at once?

Looking at the logs, obviously db2cmd.exe is throwing out a return code of 5 with exception code SQL1042C which means:



And oh shit, what the hell is the meaning of return code 5?

Tried to google, lookup IBM forums, contacted IBM Support and no one can tell me the definition of code 5?

The real fun is almost all other DB2/DWE components are running fine. db2cc, db2hc, db2start, db2stop, db2licm and many others I tried works. It seems like db2cmd.exe being cursed.

I did many other novel investigations. To name a few:

1. Rule out Windows environment variables
- Open up an instance of command prompt, clear off every environment variable.
- Run db2cmd.exe, still failed with the same error message.

2. Rule out Windows registry
- Open up regedit, throw away \HKEY_LOCAL_MACHINE\SOFTWARE\IBM and one under Current User(Of course, you backup it first)
- Run db2cmd.exe, still failed with the same error message.

3. Rule out corrupted files #1
- Copy db2cmd.exe and all the necessary dll files from my machine into the server.
- Open up command prompt, and point every thing to my new db2cmd.exe folder
- Run db2cmd.exe, still failed with the same error message.

4. Rule out corrupted files #2
- Reinstall DB2 Admin Client (Same FP)
- Run db2cmd.exe, still failed with the same error message.

5. Rule out corrupted files #3
- Reinstall DB2 everything (Latest FP)
- Run db2cmd.exe, still failed with the same error message.

6. Trace through AD GPO/Windows stuffs
- Use gpmc.msc, gpresult, gpedit.msc and many windows tools
- Trace into event viewers
- Look for suspicious items. Nothing found

7. Diagnosing DB2 #1
- Set DB2TEMPDIR to non-existing folder/folder where current user don't have security right to access/write
- db2cmd.exe normally will throw DB2CMD.EXE -> 0, Access denied message.
- Nope, still DB2CMD.EXE -> 5

8. Diagnosing DB2 #2
- Put DIAGLEVEL to 4, restart db2, run db2cmd.exe
- Nothing useful in db2diag.log

9. Diagnosing DB2 #3
- Use db2trc, turn it on, run db2cmd.exe, turn it off.
- Nothing useful.

10. Diagnosing DB2 #4
- Execute db2setcp and db2clpsetcp independently with success.

11. Diagnosing DB2 #5
- Fooling around using db2set, removing settings, changing value to bogus one and adding things.
- Still Error code 5 with db2cmd.exe

12. Finding what the heck is error code 5
- Open db2cmd.exe with notepad, look for any potential hints. Found a list of API used. Cross check API name with return code 5 using Google.
- Seems like return code 5 is usually associated with Access Denied error.

13. Finale
Google again "db2cmd.exe -> 5" +SQL1042C. Found one related post in a china forum. Apparently the fella who faces the problem is a student who was trying out DB2 for some homeworks or so. No solution from the post either.

Very interesting... What is the connection or similarity between a student machine in china and a production level server machine in Malaysia? Indeed, I found the answer to this question and yes, it solved the problem!

Any idea? :D

Weird IBM RPM Catastrophics Error

Our latest IBM RPM customer is opted for a relatively smaller investment in the solution, their system configuration of RPM involved the following components:

DB2 Express-C 9.5
Tomcat 5.5
JDK 5.0
Windows 2003
IBM RPM 7.1.1.2

Based on the instructions of the official guide of IBM RPM 7.1.1.2, there are some additional steps needed for the proper installation of the solution. You can refer to the guide for further information.

The installation took almost 1 day due to our unfamiliarity with such environment because we usually deal with IBM WAS and DB2 ESE. So we did it slowly to avoid unnecessary mistakes.

The installation was a successful one with no error in the logs.

However, a post installation testing procedure revealed some drastic and yet can be frigthening issues, some of them includes:

* When clicked on Investment Map in the Dashboard, it will respond with a EOleException Catastrophics error with some Windows hexadecimal code that translated to a very general error message.

* When try to save text key in the rich text control in the portlet, it will not be saved.

* More EOleException Catastrophic error in other features.

Very interesting indeed because none of the logs (Windows event, tomcat logs and DB2 logs) were unable to reveal any useful hints on the error.

We decided to open a case with IBM Support since the team will be busy doing other functional works.

The communication was established for almost 2 months with no apparent solution to the problem. We did the regular log compilation, scenario description, screen shots blah blah.... At the end, the support even suggested that the DB2 Express-C v9.5 is not supported by the RPM and thus unable to escalate it further. :-S

Since the project is almost near the completion and the risk of this error haunting us is getting more obvious and serious, the team decided to commit more efforts to fix it.

Here are the problem solving sequences we took:

Note: Our VM Test environment doesn't have the EOleException problem. Thus we believe it is environment specific, not a product bug.


1. Disable all irrelevant services in the OS and test
- Unchanged. Rule out the problem of software conflicting
- Enable back everything

2. Run the RPM Client outside the server machine, i.e. client workstation
- Unchanged. Confirmed it is server based problem. RPM Standalone and Plugins behaves the same.

3. Accidentally we had tried this scenario and found out something interesting.
We opened up the RPM Client and triggered the EOleException catastrophics error and then clicked Ok to continue. Then we recycled the Tomcat while the client was opened. The error will not triggered again and everything is fine, including the rich text control saving problem.
- This has revealed an important hint that the problem is caused by something shared between the client program and the Tomcat in the server.
- Since the client program is a Windows executable, possibly this got to do with some DLLs or something that the client program "loaded" before the Tomcat. I can't be sure since I don't have access to the source codes.

4. We checked the java.library.path from the Tomcat logs and found that there are another copy of xercesImpl.jar and xmlApi.jar in the path. To play safe, we removed these files and tested again. No luck.
- Rules out the problem of JAR class loading because logically the client program and Tomcat doesn't really use them together (I think).

5. A tip that we learnt from the Support is that we can stream out the logs generated by the client program using the "RPMStdIn.exe > logs.txt" and this log will contain information such as stored procedure called with parameters.
- By triggering the error, now the logs.txt contains the stored procedure that causes the error.

6. From 5 above, we copied the call information and executed it directly in DB2
- No errors returned from DB2. This confirmed the problem with how Tomcat or something inside Tomcat interpret or handle the SP call.

7. Confirm the JDBC Driver
- No problem as we directly used the drivers from the local DB2 Express-C.

8. Ruling out so many things and we start to suspect something more fundamental causes this, i.e. the Java execution environment. A check on the Tomcat JRE indicated the use of JRE 1.5.0.2. Cross referenced to Tomcat 5.5 and RPM 7.1.1.2 and none of the manual said anything about a particular level of J2SE 5.0 to be used.
- Since we running out of options, we decided to upgrade the JRE to 1.5.0.16 since that version was used in our VM too (LOL, we realized that too late I guess)
- Bingo, the error is no more.
- To reconfirm, we installed JRE1.5.0.2 in our VM and let the Tomcat used it and we managed to resimulate the problem, which is something that we were unable to perform since the reporting of the case to the Support.
- Note: The mode of the Tomcat in the environment is using jvm.dll. I'm not sure whether the same problem will happen with other modes.


All this havoc just because of insufficient information from the official about supported environments for RPM. Aiks.

P/S: IBM RPM guide never mentioned about the DB2 editions supported and this might be a grey area on the supportability of any RPM deployments.

Professionally, I strongly believe that CA Clarity did a great job on explicitly documented down the supported environments to the details on levels, fix packs, versions and so on. It can help to get rid of weird problem(s) like the one we encountered here.

UDF and SP in IBM RPM

IBM Rational Portfolio Manager (assuming version 7.1.1.2) is using Dynamic Linking Library (DLL) as its implementation for some of the database objects, namely stored procedure (SP) and user defined functions (UDF). More precisely the file name of this dll is IBMRPM.dll and it resides in two locations, i.e. ${DB2_INSTALL_ROOT}\FUNCTION\IBMRPM.dll and ${DB2_INSTALL_ROOT}\FUNCTION\unfenced\IBMRPM.dll.

During RPM installations/migrations, the process will define the linkage between SP/UDF to the appropriate methods in the dll and this piece of information is stored in SYSIBM.SYSROUTINES table's IMPLEMENTATION column.

The problem with IBM RPM installation is that it hardcoded the directory information in IMPLEMENTATION column. This shall causes portability issue when you desire to move the system to another environment where the ${DB2_INSTALL_ROOT} is not consistent with each other. For example, your initial RPM machine might have DB2 at drive C then the latest environment put it in drive E.

In such a setting, after you restored the database successfully into the new environment, you shall get alerts and some errors in the application server's logs specifying database errors with SQLSTATE 42724 REASON CODE 4.

You maybe be tempted to update the path information directly in SYSIBM but the column is only updateable through a proper DROP/CREATE PROCEDURE or ALTER PROCEDURE and in this case the number of objects to change is way too many even if you plan to write a batch script for it.

A shortcut to this problem is to copy the IBMRPM.dll and create the necessary dummy folders that reflect the original path information and paste them there. This works but it's not recommended because it might causes complication next time when doing patching or upgrading of RPM.

The best way to resolve this is to reuse some of the scripts from the installer.

Note: Please do this at your own risk. Backup everything first.


Step 1: Setting Up Environment

To do this, open up an instance of DB2CMD.

Change Directory (CD) to ${RPM_INSTALLER}\Database\DB2\Windows\CSP

If you are using DB2 V9, Please set the following environment variables using:

SET DB2TEMPDIR=${DB2_INSTALL_ROOT}\

Note: Please make sure the above path end with a slash (\)

Then

SET RPMDLLNAME=IBMRPM

You will then login to DB2 by using the user name where the name is the schema name of the objects. For example, DB2ADMIN. If you are not sure what name is that, you can always open up the control center and browse the list of stored procedures and check the schema name column. A common procedure is the SP_LOGON.

db2 connect to MYRPM user db2admin using your_password

Step 2: Recreate SP/UDF

Run the drop_sp.bat by using the following command:

drop_sp.bat > drop_sp.log

Check the drop_sp.log to verify the completion.

Run the create_sp.bat by using the following command:

create_sp.bat > create_sp.log

Check the create_sp.log to verify the completion.

Step 3: Bind packages

Because the dropping of previous SP/UDF invalidated package objects used by RPM, you need to bind them again. Open bindall.bat using notepad or other editor. Copy the for loop command and paste it in a new file. Name the file bindall2.bat. Change the value for QUALIFIER to DB2ADMIN. Save it and run the following command using the previous DB2 Command Window session.

bindall2.bat > bindall2.log

Check the bindall2.log to verify the completion.

You might want to recycle your DB2 process to get a fresh start.

db2diag.log hexadecimal error number to DB2 SQLCODE

The error number conversion table below is intended for the hex error # in db2diag.log to db2 sqlcode. For example, the hex error number ending with 80D3 can be looked up by db2 ? command using SQL4977N sqlcode. -4977 is equivalent to 4977N (N for negative).

Sorry for the messy of the following list. Copied it from somewhere. You can click on the post title to go to IBM site that has this list.

Return Code  SQL State  Reason Code  Description  

80D3  -4977   Invalid export directory specified  

80D4  -4978   Dropped table cannot be accessed  

80D5  -4979   Unable to export the dropped table data  

80D6  -1620  1  Unable to flush event monitor because it wasn't started  

80D7  -1620  2  Unable to flush event monitor because it is running at a pre-version 6 output level  

811E  -996   Invalid user-specified directory  

812F  -290   Access not allowed: table space is quiesced  

8130  -290   Access not allowed: table space is load-pending  

8131  -290   Access not allowed: table space is delete-pending  

8132  -290   Access not allowed: table space is backup-pending  

8133  -290   Access not allowed: table space is rollforward-pending  

8134  -290   Access not allowed: table space is rollforward-in-progress  

8135  -290   Access not allowed: table space is recovery-pending  

8136  -290   Access not allowed: table space is disabled  

8137  -290   Access not allowed  

8138  -291   Invalid state transition  

8139  -294   Container is already being used  

813A  -295   Container names are too long  

813B  -297   Path name is too long  

813C  -298   Bad container path  

813D  -299   Duplicate container  

813E  -257   Raw device is not allowed  

813F  -258   Add containers pending  

8146  -1442   Bad container size  

8173              No dirty buffers  

81A7               Invalid alternate  

8201  +100   End of file reached  

8203  -952   I/O Interrupt  

8212  -804   Invalid request  

8303  -952   Interrupt  

8380             No term  

8381             No interrupt  

8382             No interrupt  

8383             Incompatible release level  

8384             Operation not supported  

8385              Server/requester migration level incompatible  

8386              ASP protocol error  

8387             Bad ASP object OBJDSS  

8388             Bad FDOCA object  

8389             SNA protocol error  

838A             Invalid SQL request  

838B             Cursor already open  

838C             Cursor not open  

838D             Syntax error  

838E             Invalid FDOCA descriptor  

8390             End of SQLDTAGRP  

8394             Parameter Error  

8395             Value Error  

8396            SQLDA too small  

8397            Invalid RPYDSS  

8398            Parser syntax error  

8399            FDOCA Error  

839A            End of input  

839B            Code point not found  

839C            SQLCODE set in parse  

839D            Data descriptor mismatch  

839E            Required parameter not received  

839F            Bad format  

83A0            Duplicate parameters detected  

83A3            Translation substitution  

8401  +100   End of file reached  

8403  -952   Interrupt  

8404  -950   Active cursor  

8406  -508   Invalid cursor position  

842E  -659   Check-pending state  

8443  -804   General purpose validation error  

844B  -1477 Table not available because forward recovery encountered no log operation  

8451  -680   Too many columns  

8502  -911   Deadlock encountered  

8503  -1044 Interrupt  

8544  -911   Lock timeout with transaction rollback  

8550  -913   Lock timeout with statement rollback  

856D  -902   SQLCA has been built and saved in a component-specific control block  

8574  -1035  File open error  

8575  -1015  Database needs recovery  

8576  -1042  Deadlock start error  

8577              Deadlock stop error  

8578  -1034   Recovery failed  

8579               Conditional conflict  

85A1               Backup pending  

85A2               Recovery pending  

85A4  +993   Invalid new log path  

85A5  +995   Invalid current log path  

85A6  -1267  Exist file not found  

85AA                Table space rollforward stopped  

85AB                I/O error encountered  

85AC                Duplicate entry  

85B3                Missing log extent  

85B4                Log extent is of a different size  

85B5  -1472   System clock difference exceeds max_time_diff on connect (log synchronization)  

85B6  -1473   System clock difference exceeds max_time_diff on commit  

85B7  +1474  System clock difference exceeds max_time_diff on commit (read-only)  

85B8  -276   Restore pending  

85B9  -1275 Invalid stop time for rollforward  

85BA  -1276 Invalid time to stop rollforward  

85BB  -4970 Rollforward is missing log files  

85BC  -4971 Rollforward log is already truncated  

85BD  -4972 Rollforward log path is full  

85BE  -4973 Rollforward log mismatch  

85BF  -4974 Rollforward query error  

85C0  -4975 Rollforward cancelled  

85C1  -4976 Rollforward not issued on catalog  

85C3  -4906 Table space rollforward has invalid table space set  

85C4  -1278 Table space rollforward required for rollback  

85C5  -1280 Invalid stop time for table space rollforward  

8659             Term characters not provided  

865A              Conversion table not loaded  

865B              No conversion table exists  

865C              Invalid target code page  

865D              Invalid source code page  

865F  -5123   Invalid code page  

8660  -5124   Invalid country code  

8661  -5125   Incompatible CP and CC  

8662              Invalid stored procedure  

8663              Invalid name  

8664              Expired password  

8665              Bad password  

8666              User or group description over 8 characters  

8667              User or group ID over 8 characters  

8668              Password over 8 characters  

8669              Bad group ID  

866A              Bad user ID  

866E              Not a descendant  

866F              SMG started in background  

8670              Invalid thread ID  

867F  -10nn  Dropped current directory  

86A8              Range too large  

86A9              Invalid user name  

86AA              Owner died  

86AB              Path error  

86AC              Programming error  

86AD              Exit list full  

86AE              Exit list not found  

86AF              Services not initialized  

870B  -138  Invalid SUBSTR parameter  

8711  -910   Table or table space drop pending  

8712  -804   Invalid request  

8713  -804   Bad request context  

8714  -804   Invalid section number  

8719  -811   Non-unique answer  

871D  -994   Savepoint error  

871E  -996   Invalid directory, involving the path length of the REORG directory  

874A  -1476 Forced rollback on unit of work because of an error on number of log tables  

876B              Authorization error  

876C              Authorization with different ID  

876D  -902   SQLCA has been built and saved in a component-specific control block  

877E              RDS error  

8803  -1044   Interrupt  

8905  -803   Duplicate key  

8971              Index scan incomplete  

897A              Index end of file  

897B              Index internal error  

897D              User ID suspended  

8A72              Compilation error  

8B07  -302   Output truncated  

8B08  -413   Values out of range  

8B09  -304   Conversion overflow converting date or time to character string  

8B0A  -303   Incompatible types  

8B0C  -309   Null input invalid  

8B0D  -305   Null output invalid  

8B0E  -804   Invalid output type  

8B0F  -822   Invalid input address  

8B10  -802   Math overflow  

8B15  -311   Negative SQLDA length  

8B16  -301   Incompatible types (dynamic)  

8B17  -180   Time or date syntax error  

8B18  -181   Time or date range error  

8B1A  -404   String column overflow  

8B1B  -406   Numeric column overflow  

8B1C  -407   Non-nullable column  

8B1F  -183   Date/time arithmetic result out of range  

8B20  -182   Date/time arithmetic duration out of range  

8B21  -176   Translate scalar run-time error  

8B22  -436   Null term missing on input CSTR  

8B47  -304   Conversion overflow (numeric value)  

8B48  -302   Numeric value out of range  

8B49  -801   Divide by zero operation not allowed  

8B4C  -420   Invalid input format  

8B4D  -410   Float string too long  

8B4E              Null-only truncation  

8B4F              Date is truncated  

8C03  -952   Interrupt  

8C05  -803   Duplicate key  

9301              Allocation failure  

9302              Conversion failure  

9303              Transaction processor (TP) not started  

9304              TP limit reached  

9305              Remote TP exit normal  

9306              Allocation failed, attempting retry  

9307              Allocation failed, no retry attempted  

9308              General allocation error  

9309              Conversion failed, no retry attempted  

930A              TP not started, no retry attempted  

930B              Remote TP abend  

930C              Communications Manager not loaded  

930D              Communications Manager abend  

930E              Bad security on conversation  

930F              Unknown APPC error  

9310              Communications Manager parameter bad  

9311              Communications Manager bad request state  

9312              External communications error  

9313              Remote TP send error  

9332              Invalid name or name number  

9333              Invalid session ID  

9334              Invalid command  

9335              Invalid data  

9336              Adapter error  

9337              Reset error  

9338              Other NetBIOS error  

9364              Node environment is corrupted  

960C              Communications Manager not loaded  

960F              Generic APPC error  

9617              Already connected  

9618              Connection in progress  

9619              Connection refused  

962A              Connection timed out  

962B              Address already in use  

962C              No connection  

962D              Socket is bound already  

962E              Socket is not bound yet  

962F              Socket not writable/readable yet  

9630              Partial message was sent  

9631              Partial message was received  

9C14              FCM communication error  

9C15              FCM node not found  

9C16  -1229   Node recovery  

A602  -901   Invalid memory address  

A603  -901   General memory management error  

A604  -901   Memory management error: invalid size  

AB01  -901   Internal error  

AC01  -901   Internal program error  

C107  -986   File error  

C109  -8100 Segmented tables, page number too high  

C119  -995   EMP indirect not found  

C11A  -995   EMP map information ended  

C11B  -995   EMP map information not found  

C11C  -996   Mapping information should exist, but cannot be found  

C11D  -292   Cannot create file  

C11E  -293   Container not accessible  

C180              Agent file close error  

C201  -970   Access denied  

C211              Seek error  

C212              Unknown media error  

C213              File not found  

C214              File already exists  

C215              File in use  

C216              Invalid file name  

C217              Commit failed  

C218              Undo failed  

C40A  -659     Maximum object size reached  

C47E              Index needs to be recreated  

C47F              File renamed  

C57F              File renamed  

C601  -970   Access denied  

C602  -972   Change disk  

C603  -972   Not a DOS disk  

C604  -974   Drive locked  

C605  -976   Device not ready  

C606  -978   File write-protected  

C607  -986   File error  

C608  -902   Delete directory error  

C67C              Device is busy  

C721  -2423   Missing index during offline backup  

C90A              Maximum object size reached  

CE0B  -1614  1  Encountered an unknown event monitor target type  

CE0C  -1614  2  The event monitor target path was not sent  

CE0D  -1614  3  Access to event monitor target path was denied  

CE0E  -1614  4  Event monitor target path is not the name of a pipe  

CE0F  -1614  5  No process has opened the event monitor target pipe for reading  

CE10  -1614  6  Encountered an unexpected I/0 error.  

D085  

21  Authentication failed due to no user licenses available  

D107  -960   No more file tokens  

D121  -289   Container full  

D122              Exceeded maximum quiescers  

D123  -296   Table space limit exceeded  

D124  -259   Map too big  

D20C  -968   Disk full  

D21A  -930   No memory: UNDO heap  

D315              No memory on requester  

D316              No memory on server  

D31D              Vectored I/O request too big  

D408  -962   Maximum tables in file  

D40D  -912   Too many locks  

D411  -902   Maximum long field file size  

D505  -930   Memory allocation failure  

D509  -964   Log file full  

D50A  -912   Lock list full  

D57F  -1004 Disk full log file  

D601  -954   No memory heap (for application)  

D602  -956   No memory heap (for database)  

D603  -101   No memory heap  

D604  -930   Share buffer exceeded  

D605  -930   Memory allocation failure  

D606  -958   Too many open files  

D607  -960   No more file tokens  

D60B  -955   No memory sort heap  

D60C  -968   Disk full  

D60D  -912   Too many locks  

D60E  -973   No memory  

D610  -902   No memory BSU heap  

D612  -953   No memory: AgentHeap  

D613  -957, -959  

No memory: ComHeap  

D614  -961, -962  

No memory: RSHeap  

D615             No memory: Users/groups  

D616  -930   No memory: DrIdx heap  

D617  -930   No memory: ASP heap  

D619  -930   No memory: Queue heap  

D61A  -930   No memory: UND heap  

D61B  -930   No memory: Lock heap  

D61C  -930   No memory: System heap  

D61D  -930   No memory  

D61E  -930   No memory  

D620  -930   No memory  

D625  -930   No memory  

D62B  -930  No memory  

D67D              Shared memory set exists  

D67E  -9  No memory for DosLoadMod  

D905  -930   Memory allocation failure  

D90F  -990  Index structure problem  

DC26  -6042 No FCM MSG_ANCHOR  

DC27  -6040 No FCM buffer  

DC28  -6041 No FCM connection entry  

DC29  -6043 No FCM request block  

DC2A  -902  No high priority buffer  

E101  -980  1  Bad page  

E10A  -980  10  File does not exist  

E10E  -982   Bad signature  

E119  -1034 Page CHECKSUM error  

E11A  -1035 Bad database, won't flush it  

E11B  -980  27  Both primary and shadow ORFs are bad  

E11C  -980  28  Primary ORF is bad  

E11D  -980  29  Secondary ORF is bad  

E11E  -980  30  Both primary and shadow SSFs are bad  

E11F  -980  31  Primary SSF bad  

E120  -980  32  Secondary SSF bad  

E40B  -980  11  Object does not exist  

E50D  -980  13  Bad log file  

E510  -5123   Error in log control file  

E511  -1258   Log control file not found  

E512  -1259   I/O error accessing log control file  

E513  -1260   Database not recoverable  

E514  -1261   Recovery not pending  

E515  -1263   Invalid log extent file  

E516  -1264   Log extent file does not belong to the database  

E517  -1265   Log extent file is the incorrect version  

E518  -1266   Point in time prior to recovery  

E521  -1034   Recovery failed  

E522  -1269   Error while retrieving file during forward recovery  

E57F  -1036   Adjust log file error  

E602  -980  2  CRC error  

E603  -980  3  Disk error  

E604  -980  4  General failure  

E605  -980  5  Read fault  

E606  -980  6  Seek error  

E607  -980  7  Sector not found  

E608  -980  8  Unknown media error  

E609  -980  9  Write fault  

E60A  -980  10  File does not exist  

E60C  -980  12  Cannot open file  

E60F  -980  15  Network access denied  

E623  -931     Too many open system files  

F051  -1042   Invalid log record encountered during redo or undo: unknown component  

F102  -902  2  BPS logic error  

F103  -902  3  Invalid buffer pointer  

F104  -902  4  No buffers  

F109  -902  9  Data does not exist  

F10A  -902  10  File already exists  

F10B  -902  11  Unfixed buffer page  

F10C  -902  12  Invalid file token  

F10D  -902  13  Invalid file type  

F110  -902  16  Invalid mode parameter  

F117  -902  23  Invalid reference  

F121  -902  33  RAM semaphore error  

F124  -902  36  Bad configuration file  

F136  -902  54  Bad header  

F13B  -902  59  File not found in the reorg linked list  

F149  -902  73  Bad database path  

F156  -902  86  Page already exists  

F176  -902  118  Invalid pool ID  

F17D              State already off  

F210  -902  16  Invalid mode parameter  

F21B  -902  27  Bad transaction ID  

F225  -902  37  Invalid file handle  

F331  -902  49  Bad selector  

F33C  -902  60  Tokenizer stack overflow  

F345              The server STARTDBM failed  

F34A  -902  74  Node manager thread failed  

F34B  -902  75  Database is bad  

F34C  -902  76  Parser stack overflow  

F34D  -902  77  Token buffer overflow  

F34E  -902  78  Bad value in code page table  

F34F  -902  79  Configuration mismatch  

F401  -902  1  DMS data file error  

F418  -902  24  Bad table handle  

F419  -902  25  Bad record ID  

F42D  -902  45  Reallocation error  

F42E  -902  46  Set signal error  

F42F  -902  47  Invalid mode  

F432  -902  50  Floating point error  

F451  -1042   Invalid log record encountered during redo or undo: bad record length  

F455  -902  85  Program error  

F47A  -902  122  Record deleted  

F47C  -902  124  Missing defaults  

F51B  -902  27  Bad transaction ID  

F51C  -902  28  Log file overflow  

F51D  -902  29  Fatal logic error  

F51E  -902  30  No active transaction  

F51F  -902  31  Maximum save points  

F520  -902  32  No active save point  

F527  -902  39  Bad record type  

F528  -902  40  Transaction ID table overflow  

F529  -902  41  Invalid LSN  

F52A  -902  42  Transaction already started  

F550  -902  80  Database in recovery mode  

F605  -1068   Message file not found  

F606  -1068   Message not found  

F609  -902  9  Data does not exist  

F60A  -902  10  File already exists  

F60B  -902  11  Unfixed buffer page  

F60C  -902  12  Invalid file token  

F60D  -902  13  Invalid file type  

F60E  -902  14  Lock violation  

F60F  -902  15  Directory overflow  

F610  -902  16  Invalid mode parameter  

F611  -902  17  Invalid path  

F612  -902  18  Invalid page number  

F613  -902  19  Sector boundary error  

F614  -902  20  System internal error  

F616  -902  22  File sharing error  

F617  -902  23  Invalid reference  

F61A  -902  26  Invalid selector  

F621  -902  33  RAM semaphore error  

F622  -902  34  Access error  

F624  -902  36  Bad configuration file  

F625  -902  37  Invalid file handle  

F626  -902  38  No file descriptor  

F631  -902  49  Bad selector  

F635  -902  53  Conditional failure  

F637  -902  55  Infinite retry  

F638  -902  56  Stored procedure not found  

F639  -902  57  Invalid drive  

F63A  -902  58  Bad heap ID  

F63D  -902  61  Duplicate queue  

F63E  -902  62  Bad queue handle  

F63F  -902  63  Queue message too big  

F640  -902  64  No message in queue  

F641  -902  65  Message not sent  

F642  -902  66  Queue does not exist  

F643  -902  67  Max queue limit  

F644  -902  68  Invalid queue name  

F690              DB2NODE environment variable has a bad value  

F691              The db2nodes.cfg file contains an error  

F730  -902  48  Invalid file  

F733  -902  51  Invalid compile request  

F85F  -902  95  Generic LOB manager error  

F860  -902  96  Cannot redo operation  

F861  -902  97  Beginning segment (BSEG) already trimmed  

F862  -902  98  Insufficient space  

F863  -902  99  No mini directory  

F864  -902  100  Invalid LM descriptor  

F865  -902  101  Invalid address  

F866  -902  102  LF space exhausted  

F867  -902  103  BSEG size/address conflict  

F868  -902  104  BSEG not free or allocated  

F869  -902  105  Incorrect BSEG size  

F86A  -902  106  BSEG not free  

F86B  -902  107  Bad count array  

F86C  -902  108  Lock error  

F86D  -902  109  Not found error  

F86E  -902  110  Value out of bounds  

F86F  -902  111  Unexpected NULL value  

F870  -902  112  Encountered fatal error  

F871  -902  113  Bad state  

F872  -902  114  Request too big  

F873              No slots for threads  

F874              Thread not waiting  

F875              Owner died  

F87B              Too many active threads  

F912  -902  18  Invalid page number  

F915  -902  21  Memory allocation error  

F92B  -902  43  Index token does not exist  

F92C  -902  44  Key not found  

F952  -902  82  SMP problems  

F953  -902  83  Invalid database release  

F954  -902  84  Program error  

FB2E  -902  46  Set signal error  

FC07  -902  7  Severe internal error  

FC21  -902  33  RAM semaphore error  

FC7E  -902  126  Component interface error  

FC7F  -902  127  FCM programming error  

FC80  -902  128  FCM daemon not available  

FC81  -902  129  FCM node configuration file error  

FC88  -902  136  BDS communication error  

FC89  -902  137  BDS partner error  

FC8F  -1445   No Context  

FD8A  -902  138  Invalid partition map ID  

FD8B  -902  139  Not able to fetch from catalog  

FD8C  -902  140  Invalid data type  

FD8D              Invalid partition number  

FD8E              PMAP is of a 1-node nodegroup


More information on interpreting db2diag.log entries.

DB2 Screw Up after Removal of Windows Active Directory

For Windows 2003 machine which is used to host the AD domain controller, if ever you need to uninstall the DC and revert back to normal Windows 2003 server, you can use dcpromo tool from Windows Support CD to demote the role of the Windows.

But because the uninstallation of DC wipes out many things, particularly the user and group repositories, many of the hosted applications will not be able to execute, or even start as usual.

In a not so long ago incident, our customer decided to use one of our development VM for their testing environment before the actual setting up of the "production" version of testing server. Since we already got the VM, why not? Well, things don't turn out as expected because the VM is designed to replicate the actual production environment which includes the use of the same domain name like the one in the customer network. We got only 3 viable options, 1.) Rename the VM's domain name, 2.) Uninstall the domain controller in VM, and 3.) Recreate the VM

Since option 1 involves many manual steps as outlined in Microsoft support site and option 3 involves the most works, we decided to try out our lucks with option 2.

The dcpromo tool is very straightforward to use. You first install the Windows Support Toolkit from your Windows CD and run the dcpromo by either double click it or execute from command prompt. Then a GUI-based wizard will guide you through the rest. Restart Windows at the end.

Ho ho ho. After you restarted the Windows, you shall get a services failed message when you're in the login screen. Then after login, you shall get another DB2 starts failed message.

Do you still remember that some of DB2 servers in Windows environment are registered as Windows services and since the original user and group repositories are gone, the user name used for starting those services are no longer valid and thus you got the error message.

Assuming that your DB2 is installed with the Windows security integration option enabled, you need to recreate DB2ADMNS and DB2USERS group and db2admin (or any other admin account you specified during the installation) user account. You also need to add db2admin as Administrator. If you don't perform this step, you shall get a DB2 start error: SQL1042C SQLSTATE=58004 when tried to do a db2start execution.

Type services.msc in Run menu and sort the services list using the Log On As column. Identify the DB2 services that are using previous user accounts and reenter the new db2admin user account into these services.

Lastly, you need to grant file system rights to the DB2ADMNS and DB2USERS groups. Go to the DB2 installation directory (e.g. C:\Program Files\IBM\SQLLIB) and right lick on it and select Security tab. Remove the 2 invalid accounts and add the DB2ADMNS and DB2USERS groups and give them full control rights with propagation, in my case.

Now, you can peacefully start your DB2. Thank god!

IBM Exam 704 - Advanced DB2 DBA Certification

Taking advantage of IBM certification promotion offered to their business partners in Malaysia, I enrolled into the test 000-704 advanced DB2 DBA about less than one month ago. The test only costs me USD30. Primary study material is the "Advanced DBA Certification Guide and Reference for DB2(R) Universal Database v8 for Linux, UNIX, and Windows", ISBN #0130463884. Overall the study experience was a pleasant one because you get to dive into the rich technical details of IBM DB2 database engine and start to be amazed by this world class RDBMS.

Anyway I think that it is too naive to declare someone as certified Advanced DB2 DBA just for the sake of passing 56% out of 57 questions. Yes, the questions are breadth and tricky but it would be better if the number of questions in the test increased to let say 300, :), testing every nuances of DB2.

For me, even passing the exam with flying colors as usual, I think I'm only conquered maybe 35% to 50% of the total universe of DB2 version 8 LUW, not to mention my lack of exposures in DB2 for i5/zOS and version 9 universes.

I will work harder. :-)

DB2 Stored Procedure Maintenance

If you ever thought that you can just create a bunch of DB2 store procedures and left it in the server until it being replaced by newer version without having to spend some efforts to maintain it, you're wrong, dude/dudette.

Dynamic SQL statement is as it's name implied: Dynamic. Dynamic in the sense that the SQL Compiler process the statement when someone execute it and optimize it using the current statistics.

What about Static SQL statement? Haha, does this start to make any sense?

If it haven't ring any bells in your mind, faster go and grab a copy of DB2 book and start your revision.

Static SQL statement's access plan is generated and stored in the database at the moment that you perform the binding, i.e. compile the statement. This means that the access plan is based on the statistics at that moment.

In a large organization with few ten to hundreds line of business servers, usually the DBA don't really bother (or they can't really know) whether they must perform the maintenance on the application database objects.

Remember the DB2 Automated Maintenance Tool in DB2 Control Center? They only help you to backup, reorg and runstats your databases. Frankly speaking, there are more maintenance needs than you possibly can imagine.

Just like human body which you gotta do exercises, body building and drink super boosted tonic to maintain that drop dead gorgeous figure to attract the opposite sex, any enterprise databases desire the same treatments.

I'm going too far, but you get the idea, :p

So, the meat for today's lesson is: YOU HAVE TO REBIND YOUR STORED PROCEDURE, especially those lengthly multipages SQL codes and which involves plenty of data manipulations. One good example is a taxing month-end report generation SP.

To do this, you must either recreate the stored procedure which is I think a stupid way to perform in the long run, or you can use the System Procedure: SYSPROC.REBIND_ROUTINE_PACKAGE like the example below:

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P','MYSCHEMA.MYBATCH1','ANY');

Of course, there are few calling variants by passing in different set of parameters, but the whole point is you need to rebind the SP.

And..... very important, in case you are new in this field. Make sure you reorganize your tables (i.e. REORG command) and collect the latest statistics (i.e. RUNSTATS command) before you do the rebind, else it won't help much.

DB2, Simple right?

Connecting DB2 LUW to DB2 OS/390, Simple right?

Assuming that every necessary configurations are done at the zO/S, i.e. communication connectivity, authorizations, etc. It is blindly simple to set up DB2 LUW to talk to DB2 OS/390.

You just need to do the following in DB2 Command Window (Let say in Windows).

db2 catalog tcpip node NODE1 REMOTE SERVER OSTYPE OS390

db2 catalog dcs database AS

db2 catalog database AUTHENTICATION DCS

Ok, and if previously the utility packages are not bind yet, you might hit errors when try to run even simple commands in DB2 OS/390. To fix this, You will need to change directory to your \bnd now, and enter the following commands after you are connected to the host:

db2 bind @db2ubind.lst blocking all grant public
db2 bind @db2cli.lst blocking all grant public
db2 bind @ddcsmvs.lst blocking all grant public

Again, I will ask "Simple right?" ;-)

Using Microsoft Active Directory to Store DB2 Catalog Information

Another way to catalog many remote databases automatically is through using LDAP as the storage repository to distribute the catalog information.

In DB2 V8, there are 3 LDAP directory servers supported: IBM SecureWay Directory, Netscape LDAP Server and Microsoft Active Directory. Of these, I will briefly talking about some crucial steps to perform to eliminate obstacles as you trying to use Microsoft Active Directory to publish, store and distribute the node and database catalog information.

Assuming your domain is ibm.com and the machine host name is nerdyeddy. IP Address is 10.10.10.1 and LDAP port is listening at 389.

Firstly, fire up a DB2 Command Line Windows (db2cmd).

Execute the following commands:

db2set DB2_ENABLE_LDAP=yes
db2set DB2LDAPHOST=10.10.10.1:389
db2set DB2LDAP_BASEDN=CN=nerdyeddy,DC=ibm,DC=com
db2stop force
db2start

The above commands are to configure the LDAP connectivity to AD.

Then to register your DB2 Server into AD, do this:

db2 register ldap as MYDB2 protocol tcpip

or if you need to register a remote DB2 server, do this:

db2 register db2 server in ldap as
protocol tcpip
hostname <168.168.168.2>
svcename <50000>
remote
instance

Note: You shall need to replace the command parameters, these enclosed with square brackets accordingly.

If you was unable to run any or all of the above commands due to maybe SQL3276N eror, SQL3278N error or other funny problems, most probably you haven't run the secret setup yet, :p.

Assumining your DB2 is installed at C:\IBM\SQLLIB

Do this in the command prompt:

regsvr32 C:\IBM\SQLLIB\bin\db2ads.dll
db2schex

Basically, the db2ads.dll and db2schex are to properly configure the schema, metadata and other settings in AD to accomodate for DB2 related object classes and properties.

Ok. If your DB2 server is successfully registered in AD, then for now onwards the new databases that you created will be auto published the catalog information in AD for distribution.

If you need to catalogue previously created databases,
use command

Other related commands include:
REGISTER LDAP AS
REGISTER DB2 SERVER IN LDAP AS
CATALOG LDAP NODE
CATALOG LDAP DATABASE
UNCATALOG LDAP DATABASE
UNCATALOG LDAP NODE
DEREGISTER DB2 SERVER IN LDAP NODE
REFRESH LDAP NODE DIRECTORY
REFRESH LDAP DB DIRECTORY
ATTACH TO

Good luck.

Validate Your DB2 Backup Image

Everyone knows that a disaster recovery plan would be practically useless if you never really try the thing out... at least on regular basis. Constant environment changes and infrastructure evolutions can render your DR strategy flatten at the precise moment when you really need it to restore your business operation.

Same matters applied to your DB2 backup images (versioned backup) that you takes maybe on monthly basis depending on the criticality of the data and business continuity. So, what you plan to do? Restoring the image one by one during your precious coffee break to one of the available development machine? You must be joking me!!! Amateurs who know little about DB2 might do that.

Start pranking around and get serious. We need to use some tool to do the plumbing for us. Fortunately enough, DB2 do provides us with that tool.

DB2CKBKP

This tool is located at \bin\db2ckbkp.exe (Windows)

Have fun.

Handle Slowly Changing Dimension in DB2

Excerpt from WWW about Slowly Changing Dimension:

The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. We give an example below:

Christina is a customer with ABC Inc. She first lived in Chicago, Illinois. So, the original entry in the customer lookup table has the following record:

Customer Key Name State
1001 Christina Illinois


At a later date, she moved to Los Angeles, California on January, 2003. How should ABC Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

There are in general three ways to solve this type of problem, and they are categorized as follows:

Type 1: The new record replaces the original record. No trace of the old record exists.

Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

Type 3: The original record is modified to reflect the change.

Well, you might be naive (talented?) enough to write your own framework to manage the slowly changing dimensions in your environment, however it is good to know that there is a handy tool at your disposal to minimize the plumbing works for those data management scenarios.

As of IBM DB2 version 8.1 FixPack 2 or later, MERGE statement is introduced.

The example below illustrates how Merge can take data from a staging table and use it to update or create dimension members accordingly.

MERGE INTO
DWH.DIM_COMPANY AS TARGET
USING
(SELECT ID, NAME, PHONE FROM DWHSTAG.COMPANY) AS SOURCE
ON SOURCE.ID = TARGET.ID
WHEN MATCHED THEN
UPDATE SET (NAME,PHONE)=(SOURCE.NAME,SOURCE.PHONE)
WHEN NOT MATCHED THEN
INSERT (ID, NAME, PHONE) VALUES (SOURCE.ID, SOURCE.NAME, SOURCE.PHONE)
ELSE IGNORE;

Do you think life is better now?

Complete Syntax on DB2 Merge Statement

DB2 Changing Statement Terminator Symbol

When you pass in -t command option to DB2 CLP, the delimiter ; (Semi colon) is turn on for enabling you to enter multiple statement lines before submitting it. This is typically acceptable behavour when you need to submit a single statement that encompassing multiple lines in the console.

What if you need to submit a large and complex stored procedure creation script to CLP? You can save the codes in a physical file then use db2 -t -f myProc.sql.

Then you hit errors that doesn't make sense at all.

Most probably the query parser is confused with the statement terminator and your substatement terminator. Substatements, such as those that you embedded in your stored procedure are forced to use ; as their termination character. In this case, when the parser encounter the first embedded semicolon, it would think that the statement is ready for parsing. Ta da. it strew up.

So, a better command would be "db2 -td# -f myProc.sql" assuming you are using # symbol as your statement terminator.

Yet, you might face another issue of encountering multiple different statement terminator in the same CLP session.

So you decide to

db2 -td#
select * from syscat.tables#
quit#

db2 -td$
select * from syscat.columns$
quit$

This example is trivial, but you get my point.

So, is there a better solution? O yeah, you can use one of the DB2 Control Option in the form of:

--#SET TERMINATOR

For example:

db2 -t
SELECT * FROM SYSCAT.TABLES FETCH FIRST 1 ROW ONLY;
--#SET TERMINATOR #
SELECT * FROM SYSCAT.COLUMNS FETCH FIRST 1 ROW ONLY#
--#SET TERMINATOR $
VALUES (1)$

Similar approach can be adopted in Java DB2 programming by submitting it as part of the query you sent to DB2.

IBM DB2 HADR Example

Here is an HADR configuration example that I did when preparing for my DBA certification. It has being awhile when I performed these steps, so bear with me if there are any mistakes or errors in the description.

DB2(R) high availability disaster recovery (HADR) is a data replication feature that provides a high availability solution for both partial and complete site failures. HADR protects against data loss by replicating data changes from a source database, called the primary, to a target database, called the standby.

This practice assumes two DB2 instances, named DB2INST1 and DB2INST2 resides in the same physical machine.

For configuring HADR, you will need few pieces of information.

Update DB2 Registry Profile
DB2 Communication Protocol (DB2COMM)

Update Each Instance DBM Configuration
DB2 TCPIP Listening Port (SVCENAME)

Update Primary DB Archical Logging Configuration
Log Pages During Index Rebuild (LOGINDEXBUILD)
Index Creation Time (INDEXREC)
Retain Log (LOGRETAIN)

Update Each DB HADR Configuration
Local IP Address (HADR_LOCAL_HOST)
Local Service Name/Port (HADR_LOCAL_SVC)
Remote IP Address (HADR_REMOTE_HOST)
Remote Service Name/Port (HADR_REMOTE_SVC)
Remote DB2 Instance Name (HADR_REMOTE_INST)
Synchronization Mode (HADR_SYNCMODE)

---

For the intent of all purposes, I installed a fresh copy of DB2 ESE version 9 and chooose NOT to create the default DB2 instance. Windows environment is assumed.


Step 1: Create 2 DB2 Instances

Open up an instance of DB2 Command Window by typing "db2cmd" in Run dialog.

Create Instance 1 by typing "db2icrt DB2INST1"

Create Instance 1 by typing "db2icrt DB2INST2"


Step 2: Configure Instance 1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME 41001
db2start


Note: The TCPIP service port for this instance is 41001

Step 3: Configure Instance 2

Type the following commands:

SET DB2INSTANCE=DB2INST2
db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME 42001
db2start

Note: The TCPIP service port for this instance is 42001


Step 4: Create the Primary DB in Instance 1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 create db BANKFRS
db2 update db cfg for BANKFRS using LOGINDEXBUILD on
db2 update db cfg for BANKFRS using INDEXREC RESTART
db2 update db cfg for BANKFRS using LOGRETAIN on

Note: This step will turn the DB into archival logging mode


Step 5: Create a backup of Primary DB

Type the following commands:

db2 BACKUP DB BANKFRS TO C:\TEMP

Note: This step will backup the BANKFRS db into C:\TEMP


Step 6: Restore the backup BANKFRS as Standby database

Type the following commands:

SET DB2INSTANCE=DB2INST2
db2 RESTORE DB BANKFRS FROM C:\TEMP

Note: This step will restore the database as BANKFRS in the second DB2 instance and place it into roll forward pending state.

Note: If you roll forward the BANKFRS now, you will not be able to initialize it as the standby database.


Step 7: Configure the HADR settings for the Primary DB in DB2INST1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 update alternate server for database BANKFRS using hostname localhost port 42001
db2 update db cfg for BANKFRS using hadr_local_host localhost
db2 update db cfg for BANKFRS using hadr_local_svc 41005
db2 update db cfg for BANKFRS using hadr_remote_host localhost
db2 update db cfg for BANKFRS using hadr_remote_svc 42005
db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST2
db2 update db cfg for BANKFRS using hadr_syncmode SYNC
db2 update db cfg for BANKFRS using hadr_timeout 120

Note: Primary BANKFRS HADR service port will be 41005.
Note: Standby BANKFRS HADR service port will be 42005.
Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.


Step 7: Configure the HADR settings for the Primary DB in DB2INST1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 update alternate server for database BANKFRS using hostname localhost port 41001
db2 update db cfg for BANKFRS using hadr_local_host localhost
db2 update db cfg for BANKFRS using hadr_local_svc 42005
db2 update db cfg for BANKFRS using hadr_remote_host localhost
db2 update db cfg for BANKFRS using hadr_remote_svc 41005
db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST1
db2 update db cfg for BANKFRS using hadr_syncmode SYNC
db2 update db cfg for vusing hadr_timeout 120

Note: Primary BANKFRS HADR service port will be 41005.
Note: Standby BANKFRS HADR service port will be 42005.
Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.

---

Now, you are ready to test the configuration.


Step 8: Start HADR on Standby DB IN DB2INST2

SET DB2INSTANCE=DB2INST2
db2 START HADR ON DB BANKFRS AS STANDBY


Step 9: Start HADR on Primary DB IN DB2INST1

SET DB2INSTANCE=DB2INST1
db2 START HADR ON DB BANKFRS AS PRIMARY


Step 10: Take over the Primary Role to Standby Instance

SET DB2INSTANCE=DB2INST2
db2 TAKEOVER HADR ON DB BANKFRS


Step 11: Take back the Primary Role from Standby Instance

SET DB2INSTANCE=DB2INST1
db2 TAKEOVER HADR ON DB BANKFRS

Step 12: Stop the HADR service on Primary DB

SET DB2INSTANCE=DB2INST1
db2 STOP HADR ON DB BANKFRS


Step 13: Stop the HADR service on Standby DB

SET DB2INSTANCE=DB2INST2
db2 deactivate db BANKFRS
db2 STOP HADR ON DN BANKFRS



General Notes:

You can invoke the HADR wizard from the DB2 Control Center by right clicking on the specific database and select "High Availability and Disaster Recovery" -> Setup

You can monitor the HADR status on the database by using the database snapshot monitor. "db2 get snapshot for database on BANKFRS"

Passed IBM DB2 Certification Exam 701

Officially I didn't hold a full time job as DBA in any of my previous employments, however this doesn't stop me from getting one or two DBA certifications, because my hunger for knowledge and piss off with some moments where intricacy of DB technical details hinder my architecting and design process.

Besides all the formal knowledge and information I acquainted, one of the most important experience is to further understand the scope, responsibility and difficulties that real DBA faced in their daily operational routines and when application development needs conflicted with the interest of maintaining corporate data policy and security auditing.

In the enterprise application paradigm, the only way a solution architect can architect a better solution is to completely understood and blend every piece of the jigsaw puzzle together into a coherent whole, in the right way.

That's my motivation. Now let me share my preparation process.

1.) Read up the IBM DB2 701 Preparation Materials from IBM

Nothing much to say here.

2.) Read up DB2® Universal Database™ v8 for Linux®, UNIX®, and Windows® Database Administration Certification Guide, by George Baklarz, Bill Wong

You can opt to skip the last part regarding Application Development, even though no harm for you to take a look into it. That part is more relevant when you were taking exam 703.

3.) Do at least one project dealing with DB2 DBMS

4.) Do your own exploration and experiments with some of the concepts in the materials like HADR, Roll Forward and etc.

5.) Get enough coffee and sleeps. Trade off between these 2 important constraints :-)


Lastly, I'd like you to know that DB2 V9 series of examination is already available and you should jump on the bandwagon of V9 if your company or projects are using it or potentially will. I sticked to V8 because that's the one bundled together with IBM DBW DWE V9.1 and I dealt with that version of DB2 the most.

P/S: I got a 100% score in this exam. Cheers. I finally revenged on my 98% score in exam 700. :EVIL:

DB2 Infinite Active Log Space

In DB2, when a database is created, 3 log files are allocated. They are known as primary log files. In Linux/Unix environment, they will be 1000 * 4K pages for each log file by default, whereas in Windows it is 250 * 4k pages.

By default, up to two secondary log files will be created if needed, and their size will equal that of each primary log file used. However, the total number of secondary log files allowed is also configurable (via the logsecond database configuration parameter).

You might think you can avoid running out of log space by configuring a database to use a large number of secondary log files. However, the maximum number of secondary log files allowed is 254. If the size of your log files is relatively small, you can still run out of log space quickly when transaction workloads become heavy. You should avoid allocating a large number of secondary log files, if possible, because performance is affected each time a log file has to be allocated. Ideally, you should allocate enough primary log files to handle most situations, then use just enough secondary log files to handle peak times in transaction workloads. If you're concerned about running out of log space and want to avoid allocating a large number of secondary log files, you can configure a database to use what is known as infinite logging. To enable infinite logging, simply set the database configuration parameters userexit and logsecond to YES and -1, respectively.

UPDATE: IBM DB2 Data Warehouse Edition Password Maze

A new finding on the Alphablox wsadmin.password parameter in /server/AlphabloxAnalytics/server.properties is that after you replaced the wsadmin.password.protected with wsadmin.password to change the password, you will need to make some (dummy) changes to the Administration page in Alphablox Console for the plain text password to be automatically encrypted by Alphablox. Restart of applications or server is not necessary.

DB2 Audit Facility for Dummy

IT security auditors came and approached me, asking about DB2 user query activities, security events and blah blah. Let me see, o yeah, we got application specific audit trails. HR system got their own, ERP too and not forgetting that small little ETL program that IS people wrote. Of course, having domain specific audit facility is not a sin and they are one of the standard practice. However, DB2 provides an audit facility which is generic enough to capture almost any events happened in your databases.

Check out this DB2 program, located at <DB2_INSTALL_PATH>\bin

db2audit

Before you start to use db2audit, you should configure the AUDIT_BUF_SZ parameter in DBM configuration file. By the way, db2audit is controlled at Instance level. Setting AUDIT_BUF_SZ to non-Zero value indicates a multiple of 4KB. This is the buffer size before the audit records written to disk.

Then, you can check the current settings of db2audit, by using this command

db2audit describe

You will see something like below:

DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "TRUE "
Log checking events: "TRUE "
Log object maintenance events: "TRUE "
Log security maintenance events: "TRUE "
Log system administrator events: "TRUE "
Log validate events: "TRUE "
Log context events: "TRUE "
Return SQLCA on audit error: "TRUE "

AUD0000I Operation succeeded.

For example, if I just want to log audit events for authentication and authorization, both when success and failed, and rollback application if the system unable to generate the audit logs, perhaps due to system failure. Then I use this command:

db2audit configure scope checking,validate status both errortype audit

I will get the results below, when I submit "db2audit describe" again

DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "FALSE "
Log checking events: "TRUE "
Log object maintenance events: "FALSE "
Log security maintenance events: "FALSE "
Log system administrator events: "FALSE "
Log validate events: "TRUE "
Log context events: "FALSE "
Return SQLCA on audit error: "TRUE "

AUD0000I Operation succeeded.

You can enumerate the list of possible values for SCOPE, STATUS and ERRORTYPE by just typing "db2audit" and submit.

After configuring the audit facility, to start the facility

db2audit start

To stop the facility

db2audit stop

And, whenever you set AUDIT_BUF_SZ to non-ZERO, you should also use

db2audit flush

for writing the buffer to disk.

2 output file formats are supported by the native extraction: Flat and Delimited.

Use "db2audit extract" command to extract the logs. For example, if I need the audit records for authentication and authorization events, in comma delimiter format for database PROD, then I use the command below:

db2audit extract delasc DELIMITER , category checking, validation database

This will generates a list of files with .DEL extention in <INSTANCE_DIRECTORY>\security folder. You can load the files into database by using LOAD or IMPORT utility.

Audit file will grows over time and you need to perform house keeping on it occasionally. To remove all audit records, use

db2audit prune all

Or more likely you will want to remove records prior to certain date.

db2audit prune date YYYYMMDDHH

E.g. db2audit prune date 2007050100 will delete all records where date prior to 1-May-2007.


Lastly, additional information you might need to aware of

Only SYSADM group members can perform auditing actions

Audit Configuration File located at <INSTANCE_DIR>\security\db2audit.cfg, and it is in binary format

Audit Log File located at <INSTANCE_DIR>\security\db2audit.log, and it is in binary format

By setting DB2INSTANCE environment variable, you can configure audit for different DB2 instances

DB2 Restore DB Command

Personally, I prefer to use Restore DB DB2 Command, instead of that Restore Database Wizard in Control Center. Don't know why, maybe I easily get confused by buttons and drop downs. ;-)

Here I will run down a simulation of backing up and restoring the database under different name in separate DB2 instance resides within same physical machine. Similar scenario might be to replicate databases for development, testing and production.

First, Fire up the Command Window (db2cmd)

Create the Development instance:

db2icrt DEV

Start the DEV instance

SET DB2INSTANCE=DEV
db2start

Create the sample database

CREATE DB DEVDB

Check out the list of tablespaces in the database, this is important when later you need to redirect the creation of tablespace containers.

db2 connect to DEVDB
db2 list tablespaces

Backup the sample DEVDB database. This will create a folder DEVDB.0 under C:

db2 BACKUP DB DEVDB TO C:

Create the Production instance:

db2icrt PROD

Start the PROD instance

SET DB2INSTANCE=PROD
db2start

Restore the backup DEVDB under C: to PRODDB in PROD instance.

db2 restore db DEVDB FROM C: INTO PRODDB redirect
db2 set tablespace containers for 0 using (path 'C:\container\tspace00c1')
db2 set tablespace containers for 1 using (path 'C:\container\tspace01c1')
db2 set tablespace containers for 2 using (path 'C:\container\tspace02c1')
db2 restore db DEVDB continue

Try to connect to the new PRODDB restored from DEVDB

db2 connect to PRODDB

The trick here is to specify a separate set of container paths for Restore DB command. This will depends on number of tablespaces used by your DB. Default is only 3 tablespaces: SYS, TEMP and USER.


To clean up the simulation, do the following:

set DB2INSTANCE=DEV
db2stop force
set DB2INSTANCE=PROD
db2stop force

db2idrop DEV
db2idrop PROD

Manually remove C:\DEV, C:\PROD, C:\DEVDB.0 and C:\container