Tuesday, January 20, 2009

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 to recycle your DB2 process to get a fresh start.




Top Blogs

Friday, January 2, 2009

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 here.

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.



Top Blogs