tag:blogger.com,1999:blog-10638871915645922972024-03-05T09:52:43.885-08:00DB2 NerdUnknownnoreply@blogger.comBlogger28125tag:blogger.com,1999:blog-1063887191564592297.post-46436980719631713152009-12-16T22:26:00.001-08:002009-12-16T22:26:50.282-08:00Think Out of the Big BoxA 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:<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhq7ZicZDuEBzA14K1aTEY9pQnnSURMhEZ_1R3cDKdPb2NjP-nuMTikvx34IEyTSiNqyEHydZHWQyAtAb2abeKVbH321hQ9DukjAxkBRteV8nNuL5kjkO_4eMCfLnBkXJvVhNWMAl2zdOxh/s1600-h/1.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 211px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhq7ZicZDuEBzA14K1aTEY9pQnnSURMhEZ_1R3cDKdPb2NjP-nuMTikvx34IEyTSiNqyEHydZHWQyAtAb2abeKVbH321hQ9DukjAxkBRteV8nNuL5kjkO_4eMCfLnBkXJvVhNWMAl2zdOxh/s400/1.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5415732556357369410" /></a><br /><br />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?<br /><br />Looking at the logs, obviously db2cmd.exe is throwing out a return code of 5 with exception code SQL1042C which means:<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA9UZS5gZIBps8Ix59aAZnxMdbIFimyABBuIlmSjH7j5xddhJGeH65JHEi51WiWTiU3qsHPZWZ_QQ1uo5VItUVE72agFjRMZUh-V416ixvUdKXDEqFd4xEm5jANmThqPGXRpuMGIz2Vleh/s1600-h/2.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 397px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA9UZS5gZIBps8Ix59aAZnxMdbIFimyABBuIlmSjH7j5xddhJGeH65JHEi51WiWTiU3qsHPZWZ_QQ1uo5VItUVE72agFjRMZUh-V416ixvUdKXDEqFd4xEm5jANmThqPGXRpuMGIz2Vleh/s400/2.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5415733483684091266" /></a><br /><br />And oh shit, what the hell is the meaning of return code 5?<br /><br />Tried to google, lookup IBM forums, contacted IBM Support and no one can tell me the definition of code 5? <br /><br />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.<br /><br />I did many other novel investigations. To name a few:<br /><br />1. Rule out Windows environment variables<br />- Open up an instance of command prompt, clear off every environment variable. <br />- Run db2cmd.exe, still failed with the same error message.<br /><br />2. Rule out Windows registry<br />- Open up regedit, throw away \HKEY_LOCAL_MACHINE\SOFTWARE\IBM and one under Current User(Of course, you backup it first)<br />- Run db2cmd.exe, still failed with the same error message.<br /><br />3. Rule out corrupted files #1<br />- Copy db2cmd.exe and all the necessary dll files from my machine into the server.<br />- Open up command prompt, and point every thing to my new db2cmd.exe folder<br />- Run db2cmd.exe, still failed with the same error message.<br /><br />4. Rule out corrupted files #2<br />- Reinstall DB2 Admin Client (Same FP)<br />- Run db2cmd.exe, still failed with the same error message.<br /><br />5. Rule out corrupted files #3<br />- Reinstall DB2 everything (Latest FP)<br />- Run db2cmd.exe, still failed with the same error message.<br /><br />6. Trace through AD GPO/Windows stuffs<br />- Use gpmc.msc, gpresult, gpedit.msc and many windows tools<br />- Trace into event viewers<br />- Look for suspicious items. Nothing found<br /><br />7. Diagnosing DB2 #1<br />- Set DB2TEMPDIR to non-existing folder/folder where current user don't have security right to access/write<br />- db2cmd.exe normally will throw DB2CMD.EXE -> 0, Access denied message.<br />- Nope, still DB2CMD.EXE -> 5<br /><br />8. Diagnosing DB2 #2 <br />- Put DIAGLEVEL to 4, restart db2, run db2cmd.exe<br />- Nothing useful in db2diag.log<br /><br />9. Diagnosing DB2 #3<br />- Use db2trc, turn it on, run db2cmd.exe, turn it off.<br />- Nothing useful.<br /><br />10. Diagnosing DB2 #4<br />- Execute db2setcp and db2clpsetcp independently with success.<br /><br />11. Diagnosing DB2 #5<br />- Fooling around using db2set, removing settings, changing value to bogus one and adding things.<br />- Still Error code 5 with db2cmd.exe<br /><br />12. Finding what the heck is error code 5<br />- 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. <br />- Seems like return code 5 is usually associated with Access Denied error.<br /><br />13. Finale<br />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.<br /><br />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!<br /><br />Any idea? :D<br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com40tag:blogger.com,1999:blog-1063887191564592297.post-86106538398137249312009-02-11T01:58:00.000-08:002009-02-11T01:59:18.719-08:00Weird IBM RPM Catastrophics Error<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrbz4HeqGqiDiNEkcJJNW7BtFw7MCv2ab6ytOK6A5E_LBGUzBN7q_FXpvgGOrxsUlGAW9yu3Ynvd1e1cN_scy4WnKhQ0gl1nX2Urfh0ils5aygnh7Scdut1UrCXknc9jjrTVMZi4vQcGAK/s1600-h/monkey_face.bmp"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 298px; height: 400px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrbz4HeqGqiDiNEkcJJNW7BtFw7MCv2ab6ytOK6A5E_LBGUzBN7q_FXpvgGOrxsUlGAW9yu3Ynvd1e1cN_scy4WnKhQ0gl1nX2Urfh0ils5aygnh7Scdut1UrCXknc9jjrTVMZi4vQcGAK/s400/monkey_face.bmp" border="0" alt=""id="BLOGGER_PHOTO_ID_5301476884102033330" /></a><br /><br />Our latest IBM RPM customer is opted for a relatively smaller investment in the solution, their system configuration of RPM involved the following components:<br /><br />DB2 Express-C 9.5<br />Tomcat 5.5<br />JDK 5.0<br />Windows 2003<br />IBM RPM 7.1.1.2<br /><br />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.<br /><br />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.<br /><br />The installation was a successful one with no error in the logs.<br /><br />However, a post installation testing procedure revealed some drastic and yet can be frigthening issues, some of them includes:<br /><br />* 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.<br /><br />* When try to save text key in the rich text control in the portlet, it will not be saved.<br /><br />* More EOleException Catastrophic error in other features.<br /><br />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.<br /><br />We decided to open a case with IBM Support since the team will be busy doing other functional works.<br /><br />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<br /><br />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.<br /><br />Here are the problem solving sequences we took:<br /><br />Note: Our VM Test environment doesn't have the EOleException problem. Thus we believe it is environment specific, not a product bug.<br /><br /><br />1. Disable all irrelevant services in the OS and test<br />- Unchanged. Rule out the problem of software conflicting<br />- Enable back everything<br /><br />2. Run the RPM Client outside the server machine, i.e. client workstation<br />- Unchanged. Confirmed it is server based problem. RPM Standalone and Plugins behaves the same.<br /><br />3. Accidentally we had tried this scenario and found out something interesting.<br />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. <br />- This has revealed an important hint that the problem is caused by something shared between the client program and the Tomcat in the server.<br />- 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.<br /><br />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.<br />- Rules out the problem of JAR class loading because logically the client program and Tomcat doesn't really use them together (I think).<br /><br />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. <br />- By triggering the error, now the logs.txt contains the stored procedure that causes the error. <br /><br />6. From 5 above, we copied the call information and executed it directly in DB2<br />- No errors returned from DB2. This confirmed the problem with how Tomcat or something inside Tomcat interpret or handle the SP call.<br /><br />7. Confirm the JDBC Driver<br />- No problem as we directly used the drivers from the local DB2 Express-C.<br /><br />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. <br />- 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)<br />- Bingo, the error is no more.<br />- 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.<br /><br /><br />All this havoc just because of insufficient information from the official about supported environments for RPM. Aiks.<br /><br />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.<br /><br />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.<br /><br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-36892701108390319852009-01-20T02:14:00.001-08:002009-01-20T02:14:37.730-08:00UDF and SP in IBM RPMIBM 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.<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />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. <br /><br />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.<br /><br />The best way to resolve this is to reuse some of the scripts from the installer. <br /><br />Note: Please do this at your own risk. Backup everything first.<br /><br /><br /><b><u>Step 1: Setting Up Environment</u></b><br /><br /><blockquote><br />To do this, open up an instance of DB2CMD.<br /><br />Change Directory (CD) to ${RPM_INSTALLER}\Database\DB2\Windows\CSP<br /><br />If you are using DB2 V9, Please set the following environment variables using:<br /><br /><b>SET DB2TEMPDIR=${DB2_INSTALL_ROOT}\</b><br /><br />Note: Please make sure the above path end with a slash (\)<br /><br />Then <br /><br /><b>SET RPMDLLNAME=IBMRPM</b><br /><br />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.<br /><br /><b>db2 connect to MYRPM user db2admin using your_password</b><br /><br /></blockquote><br /><br />Step 2: Recreate SP/UDF<br /><br /><br /><blockquote><br /><br />Run the drop_sp.bat by using the following command:<br /><br /><b>drop_sp.bat > drop_sp.log</b><br /><br />Check the drop_sp.log to verify the completion.<br /><br />Run the create_sp.bat by using the following command:<br /><br /><b>create_sp.bat > create_sp.log</b><br /><br />Check the create_sp.log to verify the completion.<br /><br /></blockquote><br /><br /><br /><blockquote><br />Step 3: Bind packages<br /><br />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.<br /><br /><b>bindall2.bat > bindall2.log</b><br /><br />Check the bindall2.log to verify the completion.<br /><br /></blockquote><br /><br /><br />You might to recycle your DB2 process to get a fresh start.<br /><br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-5742277172885186722009-01-02T02:00:00.000-08:002009-01-02T02:01:21.092-08:00db2diag.log hexadecimal error number to DB2 SQLCODEThe 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).<br /><br />Sorry for the messy of the following list. Copied it from somewhere. You can click on the post title to go to IBM site <a href="http://webdocs.caspur.it/ibm_doc/udb-6.1/db2p0/db2p0107.htm#HDRRCODES">here</a>.<br /><br />Return Code SQL State Reason Code Description <br /><br />80D3 -4977 Invalid export directory specified <br /><br />80D4 -4978 Dropped table cannot be accessed <br /><br />80D5 -4979 Unable to export the dropped table data <br /><br />80D6 -1620 1 Unable to flush event monitor because it wasn't started <br /><br />80D7 -1620 2 Unable to flush event monitor because it is running at a pre-version 6 output level <br /><br />811E -996 Invalid user-specified directory <br /><br />812F -290 Access not allowed: table space is quiesced <br /><br />8130 -290 Access not allowed: table space is load-pending <br /><br />8131 -290 Access not allowed: table space is delete-pending <br /><br />8132 -290 Access not allowed: table space is backup-pending <br /><br />8133 -290 Access not allowed: table space is rollforward-pending <br /><br />8134 -290 Access not allowed: table space is rollforward-in-progress <br /><br />8135 -290 Access not allowed: table space is recovery-pending <br /><br />8136 -290 Access not allowed: table space is disabled <br /><br />8137 -290 Access not allowed <br /><br />8138 -291 Invalid state transition <br /><br />8139 -294 Container is already being used <br /><br />813A -295 Container names are too long <br /><br />813B -297 Path name is too long <br /><br />813C -298 Bad container path <br /><br />813D -299 Duplicate container <br /><br />813E -257 Raw device is not allowed <br /><br />813F -258 Add containers pending <br /><br />8146 -1442 Bad container size <br /><br />8173 No dirty buffers <br /><br />81A7 Invalid alternate <br /><br />8201 +100 End of file reached <br /><br />8203 -952 I/O Interrupt <br /><br />8212 -804 Invalid request <br /><br />8303 -952 Interrupt <br /><br />8380 No term <br /><br />8381 No interrupt <br /><br />8382 No interrupt <br /><br />8383 Incompatible release level <br /><br />8384 Operation not supported <br /><br />8385 Server/requester migration level incompatible <br /><br />8386 ASP protocol error <br /><br />8387 Bad ASP object OBJDSS <br /><br />8388 Bad FDOCA object <br /><br />8389 SNA protocol error <br /><br />838A Invalid SQL request <br /><br />838B Cursor already open <br /><br />838C Cursor not open <br /><br />838D Syntax error <br /><br />838E Invalid FDOCA descriptor <br /><br />8390 End of SQLDTAGRP <br /><br />8394 Parameter Error <br /><br />8395 Value Error <br /><br />8396 SQLDA too small <br /><br />8397 Invalid RPYDSS <br /><br />8398 Parser syntax error <br /><br />8399 FDOCA Error <br /><br />839A End of input <br /><br />839B Code point not found <br /><br />839C SQLCODE set in parse <br /><br />839D Data descriptor mismatch <br /><br />839E Required parameter not received <br /><br />839F Bad format <br /><br />83A0 Duplicate parameters detected <br /><br />83A3 Translation substitution <br /><br />8401 +100 End of file reached <br /><br />8403 -952 Interrupt <br /><br />8404 -950 Active cursor <br /><br />8406 -508 Invalid cursor position <br /><br />842E -659 Check-pending state <br /><br />8443 -804 General purpose validation error <br /><br />844B -1477 Table not available because forward recovery encountered no log operation <br /><br />8451 -680 Too many columns <br /><br />8502 -911 Deadlock encountered <br /><br />8503 -1044 Interrupt <br /><br />8544 -911 Lock timeout with transaction rollback <br /><br />8550 -913 Lock timeout with statement rollback <br /><br />856D -902 SQLCA has been built and saved in a component-specific control block <br /><br />8574 -1035 File open error <br /><br />8575 -1015 Database needs recovery <br /><br />8576 -1042 Deadlock start error <br /><br />8577 Deadlock stop error <br /><br />8578 -1034 Recovery failed <br /><br />8579 Conditional conflict <br /><br />85A1 Backup pending <br /><br />85A2 Recovery pending <br /><br />85A4 +993 Invalid new log path <br /><br />85A5 +995 Invalid current log path <br /><br />85A6 -1267 Exist file not found <br /><br />85AA Table space rollforward stopped <br /><br />85AB I/O error encountered <br /><br />85AC Duplicate entry <br /><br />85B3 Missing log extent <br /><br />85B4 Log extent is of a different size <br /><br />85B5 -1472 System clock difference exceeds max_time_diff on connect (log synchronization) <br /><br />85B6 -1473 System clock difference exceeds max_time_diff on commit <br /><br />85B7 +1474 System clock difference exceeds max_time_diff on commit (read-only) <br /><br />85B8 -276 Restore pending <br /><br />85B9 -1275 Invalid stop time for rollforward <br /><br />85BA -1276 Invalid time to stop rollforward <br /><br />85BB -4970 Rollforward is missing log files <br /><br />85BC -4971 Rollforward log is already truncated <br /><br />85BD -4972 Rollforward log path is full <br /><br />85BE -4973 Rollforward log mismatch <br /><br />85BF -4974 Rollforward query error <br /><br />85C0 -4975 Rollforward cancelled <br /><br />85C1 -4976 Rollforward not issued on catalog <br /><br />85C3 -4906 Table space rollforward has invalid table space set <br /><br />85C4 -1278 Table space rollforward required for rollback <br /><br />85C5 -1280 Invalid stop time for table space rollforward <br /><br />8659 Term characters not provided <br /><br />865A Conversion table not loaded <br /><br />865B No conversion table exists <br /><br />865C Invalid target code page <br /><br />865D Invalid source code page <br /><br />865F -5123 Invalid code page <br /><br />8660 -5124 Invalid country code <br /><br />8661 -5125 Incompatible CP and CC <br /><br />8662 Invalid stored procedure <br /><br />8663 Invalid name <br /><br />8664 Expired password <br /><br />8665 Bad password <br /><br />8666 User or group description over 8 characters <br /><br />8667 User or group ID over 8 characters <br /><br />8668 Password over 8 characters <br /><br />8669 Bad group ID <br /><br />866A Bad user ID <br /><br />866E Not a descendant <br /><br />866F SMG started in background <br /><br />8670 Invalid thread ID <br /><br />867F -10nn Dropped current directory <br /><br />86A8 Range too large <br /><br />86A9 Invalid user name <br /><br />86AA Owner died <br /><br />86AB Path error <br /><br />86AC Programming error <br /><br />86AD Exit list full <br /><br />86AE Exit list not found <br /><br />86AF Services not initialized <br /><br />870B -138 Invalid SUBSTR parameter <br /><br />8711 -910 Table or table space drop pending <br /><br />8712 -804 Invalid request <br /><br />8713 -804 Bad request context <br /><br />8714 -804 Invalid section number <br /><br />8719 -811 Non-unique answer <br /><br />871D -994 Savepoint error <br /><br />871E -996 Invalid directory, involving the path length of the REORG directory <br /><br />874A -1476 Forced rollback on unit of work because of an error on number of log tables <br /><br />876B Authorization error <br /><br />876C Authorization with different ID <br /><br />876D -902 SQLCA has been built and saved in a component-specific control block <br /><br />877E RDS error <br /><br />8803 -1044 Interrupt <br /><br />8905 -803 Duplicate key <br /><br />8971 Index scan incomplete <br /><br />897A Index end of file <br /><br />897B Index internal error <br /><br />897D User ID suspended <br /><br />8A72 Compilation error <br /><br />8B07 -302 Output truncated <br /><br />8B08 -413 Values out of range <br /><br />8B09 -304 Conversion overflow converting date or time to character string <br /><br />8B0A -303 Incompatible types <br /><br />8B0C -309 Null input invalid <br /><br />8B0D -305 Null output invalid <br /><br />8B0E -804 Invalid output type <br /><br />8B0F -822 Invalid input address <br /><br />8B10 -802 Math overflow <br /><br />8B15 -311 Negative SQLDA length <br /><br />8B16 -301 Incompatible types (dynamic) <br /><br />8B17 -180 Time or date syntax error <br /><br />8B18 -181 Time or date range error <br /><br />8B1A -404 String column overflow <br /><br />8B1B -406 Numeric column overflow <br /><br />8B1C -407 Non-nullable column <br /><br />8B1F -183 Date/time arithmetic result out of range <br /><br />8B20 -182 Date/time arithmetic duration out of range <br /><br />8B21 -176 Translate scalar run-time error <br /><br />8B22 -436 Null term missing on input CSTR <br /><br />8B47 -304 Conversion overflow (numeric value) <br /><br />8B48 -302 Numeric value out of range <br /><br />8B49 -801 Divide by zero operation not allowed <br /><br />8B4C -420 Invalid input format <br /><br />8B4D -410 Float string too long <br /><br />8B4E Null-only truncation <br /><br />8B4F Date is truncated <br /><br />8C03 -952 Interrupt <br /><br />8C05 -803 Duplicate key <br /><br />9301 Allocation failure <br /><br />9302 Conversion failure <br /><br />9303 Transaction processor (TP) not started <br /><br />9304 TP limit reached <br /><br />9305 Remote TP exit normal <br /><br />9306 Allocation failed, attempting retry <br /><br />9307 Allocation failed, no retry attempted <br /><br />9308 General allocation error <br /><br />9309 Conversion failed, no retry attempted <br /><br />930A TP not started, no retry attempted <br /><br />930B Remote TP abend <br /><br />930C Communications Manager not loaded <br /><br />930D Communications Manager abend <br /><br />930E Bad security on conversation <br /><br />930F Unknown APPC error <br /><br />9310 Communications Manager parameter bad <br /><br />9311 Communications Manager bad request state <br /><br />9312 External communications error <br /><br />9313 Remote TP send error <br /><br />9332 Invalid name or name number <br /><br />9333 Invalid session ID <br /><br />9334 Invalid command <br /><br />9335 Invalid data <br /><br />9336 Adapter error <br /><br />9337 Reset error <br /><br />9338 Other NetBIOS error <br /><br />9364 Node environment is corrupted <br /><br />960C Communications Manager not loaded <br /><br />960F Generic APPC error <br /><br />9617 Already connected <br /><br />9618 Connection in progress <br /><br />9619 Connection refused <br /><br />962A Connection timed out <br /><br />962B Address already in use <br /><br />962C No connection <br /><br />962D Socket is bound already <br /><br />962E Socket is not bound yet <br /><br />962F Socket not writable/readable yet <br /><br />9630 Partial message was sent <br /><br />9631 Partial message was received <br /><br />9C14 FCM communication error <br /><br />9C15 FCM node not found <br /><br />9C16 -1229 Node recovery <br /><br />A602 -901 Invalid memory address <br /><br />A603 -901 General memory management error <br /><br />A604 -901 Memory management error: invalid size <br /><br />AB01 -901 Internal error <br /><br />AC01 -901 Internal program error <br /><br />C107 -986 File error <br /><br />C109 -8100 Segmented tables, page number too high <br /><br />C119 -995 EMP indirect not found <br /><br />C11A -995 EMP map information ended <br /><br />C11B -995 EMP map information not found <br /><br />C11C -996 Mapping information should exist, but cannot be found <br /><br />C11D -292 Cannot create file <br /><br />C11E -293 Container not accessible <br /><br />C180 Agent file close error <br /><br />C201 -970 Access denied <br /><br />C211 Seek error <br /><br />C212 Unknown media error <br /><br />C213 File not found <br /><br />C214 File already exists <br /><br />C215 File in use <br /><br />C216 Invalid file name <br /><br />C217 Commit failed <br /><br />C218 Undo failed <br /><br />C40A -659 Maximum object size reached <br /><br />C47E Index needs to be recreated <br /><br />C47F File renamed <br /><br />C57F File renamed <br /><br />C601 -970 Access denied <br /><br />C602 -972 Change disk <br /><br />C603 -972 Not a DOS disk <br /><br />C604 -974 Drive locked <br /><br />C605 -976 Device not ready <br /><br />C606 -978 File write-protected <br /><br />C607 -986 File error <br /><br />C608 -902 Delete directory error <br /><br />C67C Device is busy <br /><br />C721 -2423 Missing index during offline backup <br /><br />C90A Maximum object size reached <br /><br />CE0B -1614 1 Encountered an unknown event monitor target type <br /><br />CE0C -1614 2 The event monitor target path was not sent <br /><br />CE0D -1614 3 Access to event monitor target path was denied <br /><br />CE0E -1614 4 Event monitor target path is not the name of a pipe <br /><br />CE0F -1614 5 No process has opened the event monitor target pipe for reading <br /><br />CE10 -1614 6 Encountered an unexpected I/0 error. <br /><br />D085 <br /><br /> 21 Authentication failed due to no user licenses available <br /><br />D107 -960 No more file tokens <br /><br />D121 -289 Container full <br /><br />D122 Exceeded maximum quiescers <br /><br />D123 -296 Table space limit exceeded <br /><br />D124 -259 Map too big <br /><br />D20C -968 Disk full <br /><br />D21A -930 No memory: UNDO heap <br /><br />D315 No memory on requester <br /><br />D316 No memory on server <br /><br />D31D Vectored I/O request too big <br /><br />D408 -962 Maximum tables in file <br /><br />D40D -912 Too many locks <br /><br />D411 -902 Maximum long field file size <br /><br />D505 -930 Memory allocation failure <br /><br />D509 -964 Log file full <br /><br />D50A -912 Lock list full <br /><br />D57F -1004 Disk full log file <br /><br />D601 -954 No memory heap (for application) <br /><br />D602 -956 No memory heap (for database) <br /><br />D603 -101 No memory heap <br /><br />D604 -930 Share buffer exceeded <br /><br />D605 -930 Memory allocation failure <br /><br />D606 -958 Too many open files <br /><br />D607 -960 No more file tokens <br /><br />D60B -955 No memory sort heap <br /><br />D60C -968 Disk full <br /><br />D60D -912 Too many locks <br /><br />D60E -973 No memory <br /><br />D610 -902 No memory BSU heap <br /><br />D612 -953 No memory: AgentHeap <br /><br />D613 -957, -959 <br /><br /> No memory: ComHeap <br /><br />D614 -961, -962 <br /><br /> No memory: RSHeap <br /><br />D615 No memory: Users/groups <br /><br />D616 -930 No memory: DrIdx heap <br /><br />D617 -930 No memory: ASP heap <br /><br />D619 -930 No memory: Queue heap <br /><br />D61A -930 No memory: UND heap <br /><br />D61B -930 No memory: Lock heap <br /><br />D61C -930 No memory: System heap <br /><br />D61D -930 No memory <br /><br />D61E -930 No memory <br /><br />D620 -930 No memory <br /><br />D625 -930 No memory <br /><br />D62B -930 No memory <br /><br />D67D Shared memory set exists <br /><br />D67E -9 No memory for DosLoadMod <br /><br />D905 -930 Memory allocation failure <br /><br />D90F -990 Index structure problem <br /><br />DC26 -6042 No FCM MSG_ANCHOR <br /><br />DC27 -6040 No FCM buffer <br /><br />DC28 -6041 No FCM connection entry <br /><br />DC29 -6043 No FCM request block <br /><br />DC2A -902 No high priority buffer <br /><br />E101 -980 1 Bad page <br /><br />E10A -980 10 File does not exist <br /><br />E10E -982 Bad signature <br /><br />E119 -1034 Page CHECKSUM error <br /><br />E11A -1035 Bad database, won't flush it <br /><br />E11B -980 27 Both primary and shadow ORFs are bad <br /><br />E11C -980 28 Primary ORF is bad <br /><br />E11D -980 29 Secondary ORF is bad <br /><br />E11E -980 30 Both primary and shadow SSFs are bad <br /><br />E11F -980 31 Primary SSF bad <br /><br />E120 -980 32 Secondary SSF bad <br /><br />E40B -980 11 Object does not exist <br /><br />E50D -980 13 Bad log file <br /><br />E510 -5123 Error in log control file <br /><br />E511 -1258 Log control file not found <br /><br />E512 -1259 I/O error accessing log control file <br /><br />E513 -1260 Database not recoverable <br /><br />E514 -1261 Recovery not pending <br /><br />E515 -1263 Invalid log extent file <br /><br />E516 -1264 Log extent file does not belong to the database <br /><br />E517 -1265 Log extent file is the incorrect version <br /><br />E518 -1266 Point in time prior to recovery <br /><br />E521 -1034 Recovery failed <br /><br />E522 -1269 Error while retrieving file during forward recovery <br /><br />E57F -1036 Adjust log file error <br /><br />E602 -980 2 CRC error <br /><br />E603 -980 3 Disk error <br /><br />E604 -980 4 General failure <br /><br />E605 -980 5 Read fault <br /><br />E606 -980 6 Seek error <br /><br />E607 -980 7 Sector not found <br /><br />E608 -980 8 Unknown media error <br /><br />E609 -980 9 Write fault <br /><br />E60A -980 10 File does not exist <br /><br />E60C -980 12 Cannot open file <br /><br />E60F -980 15 Network access denied <br /><br />E623 -931 Too many open system files <br /><br />F051 -1042 Invalid log record encountered during redo or undo: unknown component <br /><br />F102 -902 2 BPS logic error <br /><br />F103 -902 3 Invalid buffer pointer <br /><br />F104 -902 4 No buffers <br /><br />F109 -902 9 Data does not exist <br /><br />F10A -902 10 File already exists <br /><br />F10B -902 11 Unfixed buffer page <br /><br />F10C -902 12 Invalid file token <br /><br />F10D -902 13 Invalid file type <br /><br />F110 -902 16 Invalid mode parameter <br /><br />F117 -902 23 Invalid reference <br /><br />F121 -902 33 RAM semaphore error <br /><br />F124 -902 36 Bad configuration file <br /><br />F136 -902 54 Bad header <br /><br />F13B -902 59 File not found in the reorg linked list <br /><br />F149 -902 73 Bad database path <br /><br />F156 -902 86 Page already exists <br /><br />F176 -902 118 Invalid pool ID <br /><br />F17D State already off <br /><br />F210 -902 16 Invalid mode parameter <br /><br />F21B -902 27 Bad transaction ID <br /><br />F225 -902 37 Invalid file handle <br /><br />F331 -902 49 Bad selector <br /><br />F33C -902 60 Tokenizer stack overflow <br /><br />F345 The server STARTDBM failed <br /><br />F34A -902 74 Node manager thread failed <br /><br />F34B -902 75 Database is bad <br /><br />F34C -902 76 Parser stack overflow <br /><br />F34D -902 77 Token buffer overflow <br /><br />F34E -902 78 Bad value in code page table <br /><br />F34F -902 79 Configuration mismatch <br /><br />F401 -902 1 DMS data file error <br /><br />F418 -902 24 Bad table handle <br /><br />F419 -902 25 Bad record ID <br /><br />F42D -902 45 Reallocation error <br /><br />F42E -902 46 Set signal error <br /><br />F42F -902 47 Invalid mode <br /><br />F432 -902 50 Floating point error <br /><br />F451 -1042 Invalid log record encountered during redo or undo: bad record length <br /><br />F455 -902 85 Program error <br /><br />F47A -902 122 Record deleted <br /><br />F47C -902 124 Missing defaults <br /><br />F51B -902 27 Bad transaction ID <br /><br />F51C -902 28 Log file overflow <br /><br />F51D -902 29 Fatal logic error <br /><br />F51E -902 30 No active transaction <br /><br />F51F -902 31 Maximum save points <br /><br />F520 -902 32 No active save point <br /><br />F527 -902 39 Bad record type <br /><br />F528 -902 40 Transaction ID table overflow <br /><br />F529 -902 41 Invalid LSN <br /><br />F52A -902 42 Transaction already started <br /><br />F550 -902 80 Database in recovery mode <br /><br />F605 -1068 Message file not found <br /><br />F606 -1068 Message not found <br /><br />F609 -902 9 Data does not exist <br /><br />F60A -902 10 File already exists <br /><br />F60B -902 11 Unfixed buffer page <br /><br />F60C -902 12 Invalid file token <br /><br />F60D -902 13 Invalid file type <br /><br />F60E -902 14 Lock violation <br /><br />F60F -902 15 Directory overflow <br /><br />F610 -902 16 Invalid mode parameter <br /><br />F611 -902 17 Invalid path <br /><br />F612 -902 18 Invalid page number <br /><br />F613 -902 19 Sector boundary error <br /><br />F614 -902 20 System internal error <br /><br />F616 -902 22 File sharing error <br /><br />F617 -902 23 Invalid reference <br /><br />F61A -902 26 Invalid selector <br /><br />F621 -902 33 RAM semaphore error <br /><br />F622 -902 34 Access error <br /><br />F624 -902 36 Bad configuration file <br /><br />F625 -902 37 Invalid file handle <br /><br />F626 -902 38 No file descriptor <br /><br />F631 -902 49 Bad selector <br /><br />F635 -902 53 Conditional failure <br /><br />F637 -902 55 Infinite retry <br /><br />F638 -902 56 Stored procedure not found <br /><br />F639 -902 57 Invalid drive <br /><br />F63A -902 58 Bad heap ID <br /><br />F63D -902 61 Duplicate queue <br /><br />F63E -902 62 Bad queue handle <br /><br />F63F -902 63 Queue message too big <br /><br />F640 -902 64 No message in queue <br /><br />F641 -902 65 Message not sent <br /><br />F642 -902 66 Queue does not exist <br /><br />F643 -902 67 Max queue limit <br /><br />F644 -902 68 Invalid queue name <br /><br />F690 DB2NODE environment variable has a bad value <br /><br />F691 The db2nodes.cfg file contains an error <br /><br />F730 -902 48 Invalid file <br /><br />F733 -902 51 Invalid compile request <br /><br />F85F -902 95 Generic LOB manager error <br /><br />F860 -902 96 Cannot redo operation <br /><br />F861 -902 97 Beginning segment (BSEG) already trimmed <br /><br />F862 -902 98 Insufficient space <br /><br />F863 -902 99 No mini directory <br /><br />F864 -902 100 Invalid LM descriptor <br /><br />F865 -902 101 Invalid address <br /><br />F866 -902 102 LF space exhausted <br /><br />F867 -902 103 BSEG size/address conflict <br /><br />F868 -902 104 BSEG not free or allocated <br /><br />F869 -902 105 Incorrect BSEG size <br /><br />F86A -902 106 BSEG not free <br /><br />F86B -902 107 Bad count array <br /><br />F86C -902 108 Lock error <br /><br />F86D -902 109 Not found error <br /><br />F86E -902 110 Value out of bounds <br /><br />F86F -902 111 Unexpected NULL value <br /><br />F870 -902 112 Encountered fatal error <br /><br />F871 -902 113 Bad state <br /><br />F872 -902 114 Request too big <br /><br />F873 No slots for threads <br /><br />F874 Thread not waiting <br /><br />F875 Owner died <br /><br />F87B Too many active threads <br /><br />F912 -902 18 Invalid page number <br /><br />F915 -902 21 Memory allocation error <br /><br />F92B -902 43 Index token does not exist <br /><br />F92C -902 44 Key not found <br /><br />F952 -902 82 SMP problems <br /><br />F953 -902 83 Invalid database release <br /><br />F954 -902 84 Program error <br /><br />FB2E -902 46 Set signal error <br /><br />FC07 -902 7 Severe internal error <br /><br />FC21 -902 33 RAM semaphore error <br /><br />FC7E -902 126 Component interface error <br /><br />FC7F -902 127 FCM programming error <br /><br />FC80 -902 128 FCM daemon not available <br /><br />FC81 -902 129 FCM node configuration file error <br /><br />FC88 -902 136 BDS communication error <br /><br />FC89 -902 137 BDS partner error <br /><br />FC8F -1445 No Context <br /><br />FD8A -902 138 Invalid partition map ID <br /><br />FD8B -902 139 Not able to fetch from catalog <br /><br />FD8C -902 140 Invalid data type <br /><br />FD8D Invalid partition number <br /><br />FD8E PMAP is of a 1-node nodegroup<br /><br /><br /><a href="http://webdocs.caspur.it/ibm_doc/udb-6.1/db2p0/db2p052.htm">More information on interpreting db2diag.log entries.</a><br /><br /><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img alt="Top Blogs" src="http://www.blogtoplist.com/images/votebutton.gif" border="0" /></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-43651019664187811862008-12-28T03:53:00.000-08:002008-12-28T04:52:30.732-08:00DB2 Screw Up after Removal of Windows Active DirectoryFor 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.<br /><br />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.<br /><br />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<br /><br />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.<br /><br />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.<br /><br />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.<br /><br />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. <br /><br />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.<br /><br />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.<br /><br />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.<br /><br />Now, you can peacefully start your DB2. Thanks god.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-30096179670396847332008-08-22T22:23:00.001-07:002008-08-22T22:23:48.896-07:00IBM Exam 704 - Advanced DB2 DBA Certification<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb-WfIEPDTf2HIskUr41ILVh7hllNq-I20cPzUAHwf7UmfrSqMw3dMvzUKPVfpNz6OFnsd3-HyCcABqiM6cZRB1-BdIqYyKLIJ-K2puvk4NoUn5Lbv_vJfoWhRMWHn0-rfjWdXtRMJXGnh/s1600-h/exam704_result.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb-WfIEPDTf2HIskUr41ILVh7hllNq-I20cPzUAHwf7UmfrSqMw3dMvzUKPVfpNz6OFnsd3-HyCcABqiM6cZRB1-BdIqYyKLIJ-K2puvk4NoUn5Lbv_vJfoWhRMWHn0-rfjWdXtRMJXGnh/s400/exam704_result.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5231037493400843298" /></a><br /><br />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. <br /><br />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.<br /><br />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. <br /><br />I will work harder. :-)<br /><br /><br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-67414366725120499192008-08-22T22:22:00.004-07:002008-08-22T22:23:16.452-07:00DB2 Stored Procedure Maintenance<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTpL6kgMdxBSCuz3kgCEdYeGHXVVV_-5pggsEHE3w7Wrgs6GPSqL2h3GUCz-_IPlOl_NXb5BSNsUFl45EkxfXLtKNF8NXYRcYkgoXWKOXdrYWV4q9pMnbB1pW1BnrjMSxsT5k3FF_Ctwjf/s1600-h/panda.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTpL6kgMdxBSCuz3kgCEdYeGHXVVV_-5pggsEHE3w7Wrgs6GPSqL2h3GUCz-_IPlOl_NXb5BSNsUFl45EkxfXLtKNF8NXYRcYkgoXWKOXdrYWV4q9pMnbB1pW1BnrjMSxsT5k3FF_Ctwjf/s400/panda.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5228048919661293186" /></a><br />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.<br /><br />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.<br /><br />What about Static SQL statement? Haha, does this start to make any sense?<br /><br />If it haven't ring any bells in your mind, faster go and grab a copy of DB2 book and start your revision.<br /><br />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. <br /><br />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. <br /><br />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.<br /><br />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.<br /><br />I'm going too far, but you get the idea, :p<br /><br />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.<br /><br />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:<br /><br />CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P','MYSCHEMA.MYBATCH1','ANY');<br /><br />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.<br /><br />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.<br /><br />DB2, Simple right?<br /><br /><br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-73189440330078180732008-08-22T22:22:00.003-07:002008-08-22T22:22:47.112-07:00Connecting DB2 LUW to DB2 OS/390, Simple right?<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixBFiKhU1a2i1C32h_tFwU9XoGriIYyo9F95oCwqp408hfnz5C9VhY_nANzxBkLPBFoP9ZWZpU7Uiv_fWaeD4NzEu88umFs-FmAjQnBxm78ntliO5CdlGM8sBZ9VsmS5bcqIoXINQLbxhK/s1600-h/shark.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixBFiKhU1a2i1C32h_tFwU9XoGriIYyo9F95oCwqp408hfnz5C9VhY_nANzxBkLPBFoP9ZWZpU7Uiv_fWaeD4NzEu88umFs-FmAjQnBxm78ntliO5CdlGM8sBZ9VsmS5bcqIoXINQLbxhK/s400/shark.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5227260986057843266" /></a><br /><br />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.<br /><br />You just need to do the following in DB2 Command Window (Let say in Windows).<br /><br /><blockquote><br /><br />db2 catalog tcpip node NODE1 REMOTE <server_ip> SERVER <server_port> OSTYPE OS390<br /><br />db2 catalog dcs database <databaseName> AS <targetDatabaseName><br /><br />db2 catalog database <databaseName> AUTHENTICATION DCS<br /><br /></blockquote><br /><br />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 <DB2_INSTALLED_FOLDER>\bnd now, and enter the following commands after you are connected to the host:<br /><br /><blockquote><br />db2 bind @db2ubind.lst blocking all grant public<br />db2 bind @db2cli.lst blocking all grant public<br />db2 bind @ddcsmvs.lst blocking all grant public<br /></blockquote><br /><br />Again, I will ask "Simple right?" ;-)<br /><br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-44441994572566189412008-08-22T22:22:00.001-07:002008-08-22T22:22:19.356-07:00Using Microsoft Active Directory to Store DB2 Catalog Information<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinWeeLaPTnL00hNsMrlunWIwM3yn1KJCMNYgy64UUNip-vo_4FdQGXVAyJRCWfVZcHuPcg6SSE7ZxaGsly8OP3xWQ_OGOOgmzNqkzxpdM11OSJFL9R1viTvZ5aDqxTaL1LS10RNh-5BDEu/s1600-h/tony_leung_carina.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinWeeLaPTnL00hNsMrlunWIwM3yn1KJCMNYgy64UUNip-vo_4FdQGXVAyJRCWfVZcHuPcg6SSE7ZxaGsly8OP3xWQ_OGOOgmzNqkzxpdM11OSJFL9R1viTvZ5aDqxTaL1LS10RNh-5BDEu/s400/tony_leung_carina.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5225484952530698738" /></a><br /><br />Another way to catalog many remote databases automatically is through using LDAP as the storage repository to distribute the catalog information. <br /><br />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.<br /><br />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.<br /><br />Firstly, fire up a DB2 Command Line Windows (db2cmd). <br /><br />Execute the following commands:<br /><br /><blockquote><br />db2set DB2_ENABLE_LDAP=yes<br />db2set DB2LDAPHOST=10.10.10.1:389<br />db2set DB2LDAP_BASEDN=CN=nerdyeddy,DC=ibm,DC=com<br />db2stop force<br />db2start<br /></blockquote><br /><br />The above commands are to configure the LDAP connectivity to AD.<br /><br />Then to register your DB2 Server into AD, do this:<br /><br /><blockquote><br />db2 register ldap as MYDB2 protocol tcpip<br /></blockquote><br /><br />or if you need to register a remote DB2 server, do this:<br /><br />db2 register db2 server in ldap as <MYREMOTEDB2><br /> protocol tcpip<br /> hostname <168.168.168.2><br /> svcename <50000><br /> remote <remotenerdyeddy.ibm.com><br /> instance <DB2><br /><br />Note: You shall need to replace the command parameters, these enclosed with square brackets accordingly.<br /><br />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. <br /><br />Assumining your DB2 is installed at C:\IBM\SQLLIB<br /><br />Do this in the command prompt:<br /><br />regsvr32 C:\IBM\SQLLIB\bin\db2ads.dll<br />db2schex<br /><br />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. <br /><br />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. <br /><br />If you need to catalogue previously created databases, <br />use <db2 catalog ldap database> command<br /><br />Other related commands include:<br />REGISTER LDAP AS <br />REGISTER DB2 SERVER IN LDAP AS <br />CATALOG LDAP NODE <br />CATALOG LDAP DATABASE<br />UNCATALOG LDAP DATABASE<br />UNCATALOG LDAP NODE<br />DEREGISTER DB2 SERVER IN LDAP NODE <br />REFRESH LDAP NODE DIRECTORY<br />REFRESH LDAP DB DIRECTORY<br />ATTACH TO<br /><br />Good luck.<br /><br /><!-- Begin BlogToplist voting code --><br /><a href="http://www.blogtoplist.com/vote.php?u=45841" target="_blank"><br /><img src="http://www.blogtoplist.com/images/votebutton.gif" alt="Top Blogs" border="0" /></a><br /><!-- End BlogToplist voting code -->Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-1063887191564592297.post-35164286761684604302008-08-22T22:21:00.001-07:002008-08-22T22:21:50.192-07:00Validate Your DB2 Backup ImageEveryone 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. <br /><br />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. <br /><br />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. <br /><br />DB2CKBKP<br /><br />This tool is located at <DB2_INSTALL_PATH>\bin\db2ckbkp.exe (Windows)<br /><br />Have fun.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-20652553276453360472008-08-22T22:20:00.004-07:002008-08-22T22:21:17.196-07:00Handle Slowly Changing Dimension in DB2Excerpt from WWW about Slowly Changing Dimension:<br /><br /><blockquote><br /><br />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: <br /><br />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: <br /><br />Customer Key Name State <br />1001 Christina Illinois <br /><br /><br />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. <br /><br />There are in general three ways to solve this type of problem, and they are categorized as follows: <br /><br />Type 1: The new record replaces the original record. No trace of the old record exists. <br /><br />Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people. <br /><br />Type 3: The original record is modified to reflect the change. <br /><br /></blockquote><br /><br /><br />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.<br /><br />As of IBM DB2 version 8.1 FixPack 2 or later, MERGE statement is introduced.<br /><br />The example below illustrates how Merge can take data from a staging table and use it to update or create dimension members accordingly.<br /><br /><blockquote><br />MERGE INTO <br /> DWH.DIM_COMPANY AS TARGET<br /> USING <br /> (SELECT ID, NAME, PHONE FROM DWHSTAG.COMPANY) AS SOURCE<br /> ON SOURCE.ID = TARGET.ID<br /> WHEN MATCHED THEN<br /> UPDATE SET (NAME,PHONE)=(SOURCE.NAME,SOURCE.PHONE)<br /> WHEN NOT MATCHED THEN<br /> INSERT (ID, NAME, PHONE) VALUES (SOURCE.ID, SOURCE.NAME, SOURCE.PHONE)<br /> ELSE IGNORE;<br /></blockquote><br /><br />Do you think life is better now?<br /><br /><a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm">Complete Syntax on DB2 Merge Statement</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-14760483573180532632008-08-22T22:20:00.003-07:002008-08-22T22:20:46.948-07:00DB2 Changing Statement Terminator SymbolWhen 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. <br /><br />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. <br /><br />Then you hit errors that doesn't make sense at all. <br /><br />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.<br /><br />So, a better command would be "db2 -td# -f myProc.sql" assuming you are using # symbol as your statement terminator. <br /><br />Yet, you might face another issue of encountering multiple different statement terminator in the same CLP session. <br /><br />So you decide to <br /><br />db2 -td#<br />select * from syscat.tables#<br />quit#<br /><br />db2 -td$<br />select * from syscat.columns$<br />quit$<br /><br />This example is trivial, but you get my point.<br /><br />So, is there a better solution? O yeah, you can use one of the DB2 Control Option in the form of: <br /><br />--#SET TERMINATOR <TERMINATOR_SYMBOL><br /><br />For example:<br /><br />db2 -t<br />SELECT * FROM SYSCAT.TABLES FETCH FIRST 1 ROW ONLY;<br />--#SET TERMINATOR #<br />SELECT * FROM SYSCAT.COLUMNS FETCH FIRST 1 ROW ONLY#<br />--#SET TERMINATOR $<br />VALUES (1)$<br /><br />Similar approach can be adopted in Java DB2 programming by submitting it as part of the query you sent to DB2.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-90140702081116607902008-08-22T22:20:00.001-07:002008-08-22T22:20:19.514-07:00IBM DB2 HADR ExampleHere 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.<br /><br /><blockquote><br />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. <br /></blockquote><br /><br />This practice assumes two DB2 instances, named DB2INST1 and DB2INST2 resides in the same physical machine. <br /><br />For configuring HADR, you will need few pieces of information.<br /><br /><b><u>Update DB2 Registry Profile</u></b><br />DB2 Communication Protocol (DB2COMM)<br /><br /><b><u>Update Each Instance DBM Configuration</u></b><br />DB2 TCPIP Listening Port (SVCENAME)<br /><br /><b><u>Update Primary DB Archical Logging Configuration</u></b><br />Log Pages During Index Rebuild (LOGINDEXBUILD)<br />Index Creation Time (INDEXREC)<br />Retain Log (LOGRETAIN)<br /><br /><b><u>Update Each DB HADR Configuration</u></b><br />Local IP Address (HADR_LOCAL_HOST)<br />Local Service Name/Port (HADR_LOCAL_SVC)<br />Remote IP Address (HADR_REMOTE_HOST)<br />Remote Service Name/Port (HADR_REMOTE_SVC)<br />Remote DB2 Instance Name (HADR_REMOTE_INST)<br />Synchronization Mode (HADR_SYNCMODE)<br /><br /><hr /><br /><br />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.<br /><br /><br /><b><u>Step 1: Create 2 DB2 Instances</u></b><br /><br />Open up an instance of DB2 Command Window by typing "db2cmd" in Run dialog.<br /><br />Create Instance 1 by typing "db2icrt DB2INST1"<br /><br />Create Instance 1 by typing "db2icrt DB2INST2"<br /><br /><br /><b><u>Step 2: Configure Instance 1</u></b><br /><br />Type the following commands:<br /><br />SET DB2INSTANCE=DB2INST1<br />db2set DB2COMM=tcpip<br />db2 update dbm cfg using SVCENAME 41001<br />db2start<br /><br /><br />Note: The TCPIP service port for this instance is 41001<br /><br /><b><u>Step 3: Configure Instance 2</u></b><br /><br />Type the following commands:<br /><br />SET DB2INSTANCE=DB2INST2<br />db2set DB2COMM=tcpip<br />db2 update dbm cfg using SVCENAME 42001<br />db2start<br /><br />Note: The TCPIP service port for this instance is 42001<br /><br /><br /><b><u>Step 4: Create the Primary DB in Instance 1</u></b><br /><br />Type the following commands:<br /><br />SET DB2INSTANCE=DB2INST1<br />db2 create db BANKFRS<br />db2 update db cfg for BANKFRS using LOGINDEXBUILD on<br />db2 update db cfg for BANKFRS using INDEXREC RESTART<br />db2 update db cfg for BANKFRS using LOGRETAIN on<br /><br />Note: This step will turn the DB into archival logging mode<br /><br /><br /><b><u>Step 5: Create a backup of Primary DB</u></b><br /><br />Type the following commands:<br /><br />db2 BACKUP DB BANKFRS TO C:\TEMP<br /><br />Note: This step will backup the BANKFRS db into C:\TEMP<br /><br /><br /><b><u>Step 6: Restore the backup BANKFRS as Standby database</u></b><br /><br />Type the following commands:<br /><br />SET DB2INSTANCE=DB2INST2<br />db2 RESTORE DB BANKFRS FROM C:\TEMP<br /><br />Note: This step will restore the database as BANKFRS in the second DB2 instance and place it into roll forward pending state.<br /><br />Note: If you roll forward the BANKFRS now, you will not be able to initialize it as the standby database.<br /><br /><br /><b><u>Step 7: Configure the HADR settings for the Primary DB in DB2INST1</u></b><br /><br />Type the following commands:<br /><br />SET DB2INSTANCE=DB2INST1<br />db2 update alternate server for database BANKFRS using hostname localhost port 42001<br />db2 update db cfg for BANKFRS using hadr_local_host localhost<br />db2 update db cfg for BANKFRS using hadr_local_svc 41005<br />db2 update db cfg for BANKFRS using hadr_remote_host localhost<br />db2 update db cfg for BANKFRS using hadr_remote_svc 42005<br />db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST2<br />db2 update db cfg for BANKFRS using hadr_syncmode SYNC<br />db2 update db cfg for BANKFRS using hadr_timeout 120<br /><br />Note: Primary BANKFRS HADR service port will be 41005. <br />Note: Standby BANKFRS HADR service port will be 42005. <br />Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.<br /><br /><br /><b><u>Step 7: Configure the HADR settings for the Primary DB in DB2INST1</u></b><br /><br />Type the following commands:<br /><br />SET DB2INSTANCE=DB2INST1<br />db2 update alternate server for database BANKFRS using hostname localhost port 41001<br />db2 update db cfg for BANKFRS using hadr_local_host localhost<br />db2 update db cfg for BANKFRS using hadr_local_svc 42005<br />db2 update db cfg for BANKFRS using hadr_remote_host localhost<br />db2 update db cfg for BANKFRS using hadr_remote_svc 41005<br />db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST1<br />db2 update db cfg for BANKFRS using hadr_syncmode SYNC<br />db2 update db cfg for vusing hadr_timeout 120<br /><br />Note: Primary BANKFRS HADR service port will be 41005. <br />Note: Standby BANKFRS HADR service port will be 42005. <br />Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.<br /><br /><br /><hr /><br /><br />Now, you are ready to test the configuration.<br /><br /><br /><b><u>Step 8: Start HADR on Standby DB IN DB2INST2</u></b><br /><br />SET DB2INSTANCE=DB2INST2<br />db2 START HADR ON DB BANKFRS AS STANDBY<br /><br /><br /><b><u>Step 9: Start HADR on Primary DB IN DB2INST1</u></b><br /><br />SET DB2INSTANCE=DB2INST1<br />db2 START HADR ON DB BANKFRS AS PRIMARY<br /><br /><br /><b><u>Step 10: Take over the Primary Role to Standby Instance</u></b><br /><br />SET DB2INSTANCE=DB2INST2<br />db2 TAKEOVER HADR ON DB BANKFRS<br /><br /><br /><b><u>Step 11: Take back the Primary Role from Standby Instance</u></b><br /><br />SET DB2INSTANCE=DB2INST1<br />db2 TAKEOVER HADR ON DB BANKFRS<br /><br /><b><u>Step 12: Stop the HADR service on Primary DB</u></b><br /><br />SET DB2INSTANCE=DB2INST1<br />db2 STOP HADR ON DB BANKFRS<br /><br /><br /><b><u>Step 13: Stop the HADR service on Standby DB</u></b><br /><br />SET DB2INSTANCE=DB2INST2<br />db2 deactivate db BANKFRS<br />db2 STOP HADR ON DN BANKFRS<br /><br /><br /><br /><b><u>General Notes:</u></b><br /><br />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<br /><br />You can monitor the HADR status on the database by using the database snapshot monitor. "db2 get snapshot for database on BANKFRS"Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-1063887191564592297.post-10100746265560399292008-08-22T22:19:00.001-07:002008-08-22T22:19:45.199-07:00Passed IBM DB2 Certification Exam 701Officially 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. <br /><br />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. <br /><br />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.<br /><br />That's my motivation. Now let me share my preparation process. <br /><br />1.) Read up the IBM DB2 701 Preparation Materials from IBM<br /><br />Nothing much to say here. <br /><br />2.) Read up DB2® Universal Database™ v8 for Linux®, UNIX®, and Windows® Database Administration Certification Guide, by George Baklarz, Bill Wong<br /><br />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.<br /><br />3.) Do at least one project dealing with DB2 DBMS<br /><br />4.) Do your own exploration and experiments with some of the concepts in the materials like HADR, Roll Forward and etc.<br /><br />5.) Get enough coffee and sleeps. Trade off between these 2 important constraints :-)<br /><br /><br />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. <br /><br />P/S: I got a 100% score in this exam. Cheers. I finally revenged on my 98% score in exam 700. :EVIL:Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-80180311434248308902008-08-22T22:18:00.002-07:002008-08-22T22:19:15.467-07:00DB2 Infinite Active Log SpaceIn 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.<br /><br />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).<br /><br />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.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-64604767290222919172008-08-22T22:18:00.001-07:002008-08-22T22:18:39.561-07:00UPDATE: IBM DB2 Data Warehouse Edition Password MazeA 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.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-27233778433050865382008-08-22T22:17:00.002-07:002008-08-22T22:18:05.135-07:00DB2 Audit Facility for DummyIT 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.<br /><br />Check out this DB2 program, located at <DB2_INSTALL_PATH>\bin<br /><blockquote><br />db2audit<br /></blockquote><br /><br />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.<br /><br />Then, you can check the current settings of db2audit, by using this command<br /><blockquote><br />db2audit describe<br /></blockquote><br /><br />You will see something like below:<br /><br /><blockquote><br /><br />DB2 AUDIT SETTINGS:<br /><br />Audit active: "FALSE "<br />Log errors: "TRUE "<br />Log success: "TRUE "<br />Log audit events: "TRUE "<br />Log checking events: "TRUE "<br />Log object maintenance events: "TRUE "<br />Log security maintenance events: "TRUE "<br />Log system administrator events: "TRUE "<br />Log validate events: "TRUE "<br />Log context events: "TRUE "<br />Return SQLCA on audit error: "TRUE "<br /><br />AUD0000I Operation succeeded.<br /><br /></blockquote><br /><br /><br />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:<br /><br /><br /><blockquote><br />db2audit configure scope checking,validate status both errortype audit<br /></blockquote><br /><br />I will get the results below, when I submit "db2audit describe" again<br /><br /><blockquote><br />DB2 AUDIT SETTINGS:<br /><br />Audit active: "FALSE "<br />Log errors: "TRUE "<br />Log success: "TRUE "<br />Log audit events: "FALSE "<br />Log checking events: "TRUE "<br />Log object maintenance events: "FALSE "<br />Log security maintenance events: "FALSE "<br />Log system administrator events: "FALSE "<br />Log validate events: "TRUE "<br />Log context events: "FALSE "<br />Return SQLCA on audit error: "TRUE "<br /><br />AUD0000I Operation succeeded.<br /></blockquote><br /><br />You can enumerate the list of possible values for SCOPE, STATUS and ERRORTYPE by just typing "db2audit" and submit.<br /><br />After configuring the audit facility, to start the facility<br /><br /><blockquote><br />db2audit start<br /></blockquote><br /><br />To stop the facility<br /><br /><blockquote><br />db2audit stop<br /></blockquote><br /><br /><br />And, whenever you set AUDIT_BUF_SZ to non-ZERO, you should also use <br /><br /><blockquote><br />db2audit flush<br /></blockquote><br /><br />for writing the buffer to disk. <br /><br />2 output file formats are supported by the native extraction: Flat and Delimited.<br /><br />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:<br /><br /><blockquote><br />db2audit extract delasc DELIMITER , category checking, validation database<br /></blockquote><br /><br />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.<br /><br />Audit file will grows over time and you need to perform house keeping on it occasionally. To remove all audit records, use<br /><br /><blockquote><br />db2audit prune all<br /></blockquote><br /><br />Or more likely you will want to remove records prior to certain date.<br /><br /><blockquote><br />db2audit prune date YYYYMMDDHH<br /></blockquote><br /><br />E.g. db2audit prune date 2007050100 will delete all records where date prior to 1-May-2007.<br /><br /><br />Lastly, additional information you might need to aware of<br /><br /><ui><br /><li>Only SYSADM group members can perform auditing actions</li><br /><li>Audit Configuration File located at <INSTANCE_DIR>\security\db2audit.cfg, and it is in binary format</li><br /><li>Audit Log File located at <INSTANCE_DIR>\security\db2audit.log, and it is in binary format</li><br /><li>By setting DB2INSTANCE environment variable, you can configure audit for different DB2 instances</li><br /></ui>Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-1063887191564592297.post-51800792197926884312008-08-22T22:17:00.001-07:002008-08-22T22:17:28.123-07:00DB2 Restore DB CommandPersonally, 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. ;-)<br /><br />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.<br /><br />First, Fire up the Command Window (db2cmd)<br /><br />Create the Development instance: <br /><br /><blockquote>db2icrt DEV</blockquote><br /><br />Start the DEV instance<br /><br /><blockquote><br />SET DB2INSTANCE=DEV<br />db2start<br /></blockquote><br /><br />Create the sample database<br /><br /><blockquote><br />CREATE DB DEVDB<br /></blockquote><br /><br />Check out the list of tablespaces in the database, this is important when later you need to redirect the creation of tablespace containers.<br /><br /><blockquote><br />db2 connect to DEVDB<br />db2 list tablespaces<br /></blockquote><br /><br /><br />Backup the sample DEVDB database. This will create a folder DEVDB.0 under C:<br /><br /><blockquote><br />db2 BACKUP DB DEVDB TO C:<br /></blockquote><br /><br />Create the Production instance: <br /><br /><blockquote>db2icrt PROD</blockquote><br /><br />Start the PROD instance<br /><br /><blockquote><br />SET DB2INSTANCE=PROD<br />db2start<br /></blockquote><br /><br />Restore the backup DEVDB under C: to PRODDB in PROD instance.<br /><br /><blockquote><br /><br />db2 restore db DEVDB FROM C: INTO PRODDB redirect<br />db2 set tablespace containers for 0 using (path 'C:\container\tspace00c1')<br />db2 set tablespace containers for 1 using (path 'C:\container\tspace01c1')<br />db2 set tablespace containers for 2 using (path 'C:\container\tspace02c1')<br />db2 restore db DEVDB continue<br /></blockquote><br /><br /><br />Try to connect to the new PRODDB restored from DEVDB<br /><br /><blockquote><br />db2 connect to PRODDB<br /></blockquote><br /><br /><br />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.<br /><br /><br />To clean up the simulation, do the following:<br /><br /><blockquote><br />set DB2INSTANCE=DEV<br />db2stop force<br />set DB2INSTANCE=PROD<br />db2stop force<br /><br />db2idrop DEV<br />db2idrop PROD<br /><br />Manually remove C:\DEV, C:\PROD, C:\DEVDB.0 and C:\container<br /><br /></blockquote>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-11790165954609177612008-08-22T22:16:00.001-07:002008-08-22T22:16:50.671-07:00IBM Data Warehouse Edition DWH Password MazeStringent user account security policy in the network domain can cause damaging maintenance headache in deployed IBM DWH multiservers environment. User account information are all around the places, in your DB2 services, WAS server, Alphablox and so on. The day when the user passwords expired or required account disabled, it will be the day DWE solutions face total outrage. Well, may be I'm just exaggerating. <br /><br />Where do you update the user credentials in DWE environment when such a need arise?<br /><br />Briefly speaking, at least the following locations:<br /><br />1. DB2 Windows services, assuming Windows environment <br /><br />Log On As for each DB services need to be updated.<br /><br /><br />2. Websphere Global Security Setting, assuming using LocalOS repository<br /><br />This can be tricky. The easiest is to update the password before you shut down the WAS server. If the server already shut down and you didn't manage to update the password, then you wouldn't be able to start the server again because of authentication error. If this is the case, you got to manually disable the WAS global security by changing the "enabled" attribute of security:Security xml element to false in security.xml file located in <profile>/config/cells/<host>Cell. Then start the server, update the password in LocalOS setting and turn on Global Security again by checking on the option in WAS Admin Console.<br /><br />3. Data Sources defined in DWE Admin Console<br /><br />Data Sources used by DWH application processes, which are not attached to WAS data source, must be updated.<br /><br />Before you can perform this, you need to update the J2C user password in WAS for Admin Console to be able to connect to its repository. (Item 6)<br /><br />4. Data Sources defined in Alphablox Admin Console<br /><br />Usually this will be data sources for Alphablox cubes to retrieve IBM Cube Views meta data. <br /><br />5. WAS account used by Alphablox for management<br /><br />Alphablox uses a WAS user credential for connecting to WAS and managing Alphablx applications in WAS. This piece of information is located in Alphablox repository, /servers/AlphabloxAnalytics/server.properties. <br /><br />Replace the line ws.admin.password.protected with ws.admin.password=<your_password_in_plaintext><br /><br />The issue here is that the new password will be in clear text. I read across some materials that say the password is encoded again the next time the server restarted. However, I don't see that happens in my environment.<br /><br />6. WAS J2C Authentication entries<br /><br />7. WAS JNDI Data Sources, assuming not using J2C authentication<br /><br />8. Optionally, WAS Windows Services<br /><br /><br />Hope this is helpful to you.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-68280128914190558262008-08-22T22:15:00.002-07:002008-08-22T22:16:14.557-07:00Alphablox Cube SecurityI really headache when customer want to impose security constraints on Alphablox solution. Here is a simple security problem that I need to solve. <br /><br />A local bank in Malaysia has 14 main branches throughout the country where each state got one main branch. There is at least 2 groups of users with different level of data access. One group is country wide users who can view the data for all the states. Another group being state manager which can only sees their own cake.<br /><br />My first attempt is defining a user property called BelongingState and assign it state code that user belongs to, or "ALL" if they are country wide users. Then I would use Java codes to dynamically construct the MDX for the DataBlox. Thought this will solve my problem, but some user actions would causes rewrite of the MDX automatically (Seems like the default behavior). For example, Show Siblings action will display all members on the same level. This totally defeats my aim of controlling view by Malaysia state. Another one is the Drill Up action. I also found Member Filter dialog and Drill Down (There are 5 types of Drill Down available) action can causes exploitation on data. <br /><br />Due to lack of time to spend on digging deep into Alphablox object model, I revert to a quick resolution: use removeAction attribute to disable Drill Up, Member Filter and Show Siblings for state level users. At the same time, I wrote a filter on Drilldown event to check the drill down option and prevent the drill down to inappropriate data area. This cut off a lot of interactivity from user, but for the sake of security, some trade offs gotta be made. <br /><br />Giving some faith to developers who wrote Alphablox framework, I believe there should be some methods to disable MDX rewrite due to user activity, thus preventing the underlying data set from changing. One thing to note if that if you are using MSAS or Essbase as cube source, then you can rely on the native security control features of these cube engines for security. Particularly you can use MemberSecurity tag for this purpose. For Cube View based cubes, may be it could be possible to control the view at database level. <br /><br />Back to square one, my point of bringing this topic up is that it is important to plan for security requirements to match the out of the box security features provided by Alphablox. For my simple security scenario, I personally think that it is more usable to create 14 identical cubes, each for different state. By controlling which cube that users use in their report, you can be sure that the data view doesn't violate the security expectation, yet retaining powerful interactivity actions such as Member Filter and Drill Up. Things can get pretty ugly and complicated when the security requirements are not just on one dimension. In this case, creating separate cube for different data view might not be practical.<br /><br />Lastly, localization is another aspect of global business intelligence application that can be as tricky as security factor to implement in Alphablox solution. I really need to appraise Microsoft Analysis Services (MSAS) for incorporating security and localization so well in their cubes. Maybe this is what differentiates market leader and players.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-81116710941232868972008-08-22T22:15:00.001-07:002008-08-22T22:15:34.821-07:00Alphablox Features Wish ListWriting to Santa-BM,<br /><br />Undeniably, Alphablox architecture and component models present a very flexible and powerful of incorporating analytic capability into whatever web-based applications that your enterprise currently maintained. <br /><br />With greater power comes greater responsiblities are very true indeed. To be up to 80% productive with Alphablox solution, the architect and developer need to face a very steep learning curve especially if their background on design pattern, J2EE web applications, and some-what JSF-type of knowledge are not strong. <br /><br />To put it in simple form, I see Alphablox as conglomeration of part-of-JSF (There are other JSF competitors, but I wouldn't mention them here since they are not the part of standard J2EE stack) and AJAX suite of technology.<br /><br />The thing that I like JSF is not just about their wonderful architecture and well defined component framework. The most important is the promised upcoming tool support that allows you (developers or designer) to visual create, debug, test and deploy JSF component. Without this support, it's like .NET Web developer using code editor to create web pages. WYSIWYG? Forget it.<br /><br />The problem with Alphablox (As of 8.4.0.1) is the missing of these tool supports. <br />I don't get report designer in DWE design studio. I don't get testing environment for my custom made UI blox. I can't even test my report without actually put them into the server and press F5 key. Of course, some of you might argue and say to me "Please spend some $$ to buy WSAD 6, cheap skate!". Ok, I accepted that suggestion. But the point is DWE as an integrated and promised to be end-to-end BI platform should at least provide a minimal report development support. Look at Microsoft SSRS, althought their report templates are quite limited, at least I can drag a text box component on the report or define the styles of report contents visually, ;-)<br /><br />Here some constructive suggestions to IBM Alphablox team<br /><br />* Provide new project types in design studio that do<br /> 1. Alphablox Relational Report Creation (With tons of wizard, of course)<br /> 2. Alphablox Analytics Report Creation (With tons wizard, of course)<br /> 3. Alphablox Cube (With the real time capabilities to see cube data, etc)<br /> - Trust me, Query Builder is really not enough<br /> 4. Alphablox Custom Component/UI Component Creation<br /> <br />I wish that next time when I want to customize the behavior of the MemberFilterBlox to inform users that their selection is not valid due to constraints such as security, I only need to :<br /><br />1. Fire up my design studio<br />2. Open the Alphablox project that contains the web page that have the specific PresentBlox/MemberFilterBlox, <br />3. Double click on the blox, brings me a selection list of events to listen<br /><br />OR <br /><br />3. Right click on the blox, go to "Customize Event Handler", then "XXX event"<br /><br />4. Select the event, and then it generates the handler method skeleton for me to put in my code.<br /><br />All linking between the event handler and the framework event pipelines should be handled internally by the boilerplate, I don't want to be bothered about that.<br /><br />5. Lastly, from Tool Palette, drag a Message Box dialog out to the editor<br /><br />6. Skeleton message box server side codes automatically generated for me, I just need to put in the title and message, etc.<br /><br />7. Press a button/key to preview with live data.<br /><br /><br />So, think about my imaginative way and compare it to the current way of doing the same thing, which one is more developer friendly and productive you say?<br /><br />Even better, convert all the bloxs to JSF compliant component or provide a JSF wrapper for them. This will allows any JSF supported IDE to develop Alphablox applications.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-34327455914060919992008-08-22T22:14:00.000-07:002008-08-22T22:15:10.329-07:00Enable Commenting (Comments)An excerpt from Alphablox 8.4 documentation:<br /><br /><BLOCKQUOTE><br />CommentsBlox allows you to provide cell commenting (also known as cell annotations) functionality to your application. In addition, you can use CommentsBlox for general commenting that are not tied to any other Blox. For example, you can allow users to add comments to a site, an application, a report, or a Web page.<br /><br />Comments are stored in a JDBC accessible relational database. Supported databases include IBM(R) DB2(R) UDB, Sybase, Microsoft(R) SQL Server, and Oracle. This data source needs to be defined to DB2 Alphablox. DB2 Alphablox provides a Comments Management page under the Server link in the DB2 Alphablox Administration tab that lets you specify the relational data source to use for storing comments. From that page, you can create "collections" (data tables) to store comments. For cell-level comments, you will need to specify the multidimensional data source used in your GridBlox, the cube to use (for Microsoft Analysis Services), and the dimensions to include. For general comments, you only need to specify the name.<br /></BLOCKQUOTE><br /><br />Here is what you need to do to get a feel of the built-in CommentsBlox in Alphablox. <br /><br />First, go to Alphablox Administration Site, by default should be accessible from http://yourserver:9080/AlphabloxAdmin/<br /><br />From the Administration main tab, you should be in General paage by default. There is a Runtime Management section, amongst others. Click on the Comments link under this section. A pop-up window should appear that refer to http://yourserver:9080/AlphabloxAdmin/comments/commentsAdmin.jsp.<br /><br />Here you gotta define the comments collection (sort of like a repository to store a particular set of comments) and maybe customize the set of fields used for commenting.<br /><br />Choose a data source that you already defined elsewhere, enter the security credential and click Connect button. All existing collections should appear in the list provided. <br /><br /><br />Click Create button, since you want to create a new collection. You need to fill in the details for the form provided, particularly you need to specify the dimension to enable the comments and the fields required for the comment entry.<br /><br /><a href="http://photos1.blogger.com/blogger2/7866/2860/1600/comment_create.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger2/7866/2860/400/comment_create.jpg" border="0" alt="" /></a><br /><br />Click Save once you done. <br /><br />Ok, you have done with the necessary configuration. <br /><br />In your Blox programming, you can do the following:<br /><br />1. Enabling Commenting for GridBlox<br />...<br /><blox:data><blox:comments collectionName="YourCommentCollectionName" dataSourceName="TheDataSourceUsedForCollection" /></blox:data><br />...<br /><br />...<br /><blox:grid commentsEnabled="true" /><br />...<br /><br />Then when you right click on your grid cell, an option called "Comments" will appear.<br /><br /><br />2. Use com.alphablox.blox.CommentsBlox and related com.alphablox.blox.comments.* <br /><br />This allows you to implement Commenting feature for your relational reports or any other general usage. Requires programming though.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-50006808310714138482008-08-22T22:13:00.001-07:002008-08-22T22:13:56.654-07:00IBM DB2 Exam 000-700I did it in the reverse way. I took the exam 000-705, yet I still haven't take 000-700. It's time now to bridge the gap and to obtain the official title of BI Designer.<br /><br />Following are the steps I took for the preparation:<br /><br /><blockquote><br /><br />1. Read DB2 Family Fundamental Guide from IBM<br /><br />6 chapters guide. Clear and concise to help you pass above 75%.<br /><br />2. Read eLearning Crammer Course Material from colleague<br /><br />Consists some sample questions and added notes for task 1. If you read this, 80% and above shouldn't difficult.<br /><br />6 chapters guide. Clear and concise to help you pass above 75%.<br /><br />3. Read a 400 pages book<br /><br />DB2® Universal Database™ V8.1 Certification Exam 700 Study Guide<br />By Roger E. Sanders<br /><br />Must read if you plan to get 85% and above.<br /><br />4. Use DB2 whenever possible<br /><br />SQL section constitutes 31% of exam. Practice makes perfect. I wouldn't need to do this step since I'm already quite good in it. :p<br /><br />Must do if 90% and above is your target<br /><br />Note: No. No advanced SQL covered in the exam. No recursive SQL you bet!<br /><br /></blockquote><br /><br />My result sheet<br /><br /><a href="http://photos1.blogger.com/blogger/8177/2403/1600/000-700-result.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger/8177/2403/400/000-700-result.jpg" border="0" alt="" /></a><br /><br /><br />Some of the questions are pretty tricky. I shall not put the real questions here, but just to give you some clues, here is a similar one:<br /><br />(Not REAL questions)<br /><br />1.) If you gotta build DB2 application on XXX OS and it need to access YYYY OS DB2 Server when deployed, which of the following DB2 product is the minimal you need?<br /><br />a. DB2 UDB Personal Edition<br />b. DB2 UDB Personal Edition with DB2 Connect Enterprise Edition<br />c. DB2 Personal Developer Edition<br />d. DB2 Universal Developer Edition<br /><br />Answer is <b>C</b>. Because you need to <b>build</b> DB2 application, you need the SDK toolkit and at least a DB2 UDB PE for testing, therefore C is the minimal. D can be the answer is the question is not on MINIMAL because DB2 UDE includes every DB2 PDE components. A and B is definitely not the answer since it doesn't include SDK in the package.<br /><br />and another typical kind of question.<br /><br />2.) What should you catalog for a client to access a DB2 host database?<br /><br />Answer: You need to catalog the remote server in the NODE directory, the remote database in the System DB directory AND remote database in the DCS directory since you need to use DB2 Connect to access host database. You need to understand the parameter use in these CATALOG statements.<br /><br /><br />Good luck for you folks that taking this exam.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-44998121870560521742008-08-22T22:12:00.001-07:002008-08-22T22:12:43.915-07:00IBM DB2 Exam 000-705To earn the title as IBM Certified Business Intelligence Designer, I got to get myself pass in two exams. 000-700 and 000-705. I already passed the 000-705 BI exam during IBM Malaysia sponsored workshop (It's free for IBM business partner). <br /><br />Here is the summary of the 000-705 exam preparation and experience.<br /><br /><blockquote><br />1. 3 days of IBM DWE V9 bootcamp <br /><br />Hmmm, not that relevant to the 705 exam, more like IBM try to equip its' business partners presales skills on the new DB2 DWE. It covers topics like the new Design Studio, Intelligent Miner, Cube making and etc. <br /><br />2. 1 day of 705 exam workshop<br /><br />It is like a last minute top-up of the knowledge pertaining to the 705 exam objectives. The instructor covers the test objectives one by one, and goes through some sample questions.<br /><br />3. Self study<br /><br />Got myself the copy of study guide from IBM web site for BI Designer exam version 7, most of the concepts and theories are still applicable.<br /><br />Found a few excerpts from the book <br />"Business Intelligence for the Enterprise", <br />ISBN: 0-13-141303-1<br /><br />This book is only 240 pages and it tells you general "stories" of Business Intelligence. Personally, I found it not that useful for the exam, but still something interesting to read about.<br /><br /></blockquote><br /><br /><a href="http://photos1.blogger.com/blogger/8177/2403/1600/000-705-result.1.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="http://photos1.blogger.com/blogger/8177/2403/1600/000-705-result.1.jpg" border="0" alt="" width="95%" height="95%"/></a><br /><br />Scored a 87.5% or answered 49 questions correct out of total 56 questions. <br />Hit major bumper in the section "Business Intelligence Data Sourcing/Movement", which mostly the questions on replication and types of data warehouse processing (full, incremental, snapshot, etc).<br /><br />705 is not a technical paper. If you have decent experience in data warehousing and business intelligence, then questions in the terminology, requirements and architecture are piece of cake. <br /><br />Again, it is easy to pass, but hard to score and excel.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1063887191564592297.post-79225027674584098712008-08-22T22:11:00.002-07:002008-08-22T22:12:03.800-07:00DB2 How to Empty a TableIn data warehouse environment, usually there is a need to clear out the contents of staging tables to prepare for a fresh set of extracted source data. You might also want to housekeep some historical aggregation according to some predefined schedules, which involves copying data from a table to another and subsequently removed all data from the copied table. These are just some of the examples that boiled down to the need of efficiently "truncate" a table. <br /><br />Microsoft SQL Server and Oracle DBA are definitely enjoying the luxury of built-in table truncation functionality, through command like "TRUNCATE TABLE YourTableName". <br />Ok fine, but does IBM DB2 UDB, the so-called most scalable and performing RDBMS provide such option? <br /><br />Before that, let me evaluate some of the options of removing rows from a table.<br /><br />Option A: <br /><br /><u><b>DELETE FROM YourTable</b></u><br /><br />Well, you can delete all rows using this statement. However when involving lot of records, transaction logging causes significant performanc degradation. Still, this option is acceptable if your application requires recovery of deleted rows.<br /><br /><br />Option B: <br /><br /><u><b><br />(Assume this is within the same transaction)<br />ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;<br />DELETE FROM YourTable;<br /></b></u><br /><br />You managed to escape the bad luck of doing a lot of transaction logging. But wait a minute, constraint checking are still in force (Check yourself by doing explaining a DELETE FROM statement).<br /><br /><br />Option C:<br /><br /><u><b><br />(Assume this is within the same transaction)<br />SET INTEGRITY FOR YourTable OFF;<br />ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;<br />DELETE FROM YourTable;<br /></b></u><br /><br />Again, you managed to skip the logging and check constraint and referential constraint checking, datalink integrity checking, and generation of values for generated columns. Primary/Unique Key constraints still enforced. <br /><br />Option D: <br /><br /><u><b><br />LOAD FROM /dev/null of del REPLACE INTO YourTable<br /></b></u><br /><br />This is by far the most common workaround that I have seen for table truncation. It basically uses the LOAD utility on /dev/null for simulating the loading (replace) of no-data into the designated table. The same concept works for Windows environment.<br /><br />You can also use similar IMPORT FROM /dev/null of DEL REPLACE INTO YourTable. There are some differences between IMPORT/LOAD. <br /><br />Option E:<br /><br />With a little bit of guts, you can drop and recreate the tables. This can be tedious if you got to recreate every constraints/views/etc that dependent on the "new" table.<br /><br />Option F:<br /><br /><u><b><br />ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE<br /></b></u><br /><br />This is by far my favorite way of doing "TRUNCATE Table" in DB2. <br /><br />Which ring suits you? You decide.Unknownnoreply@blogger.com1