x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

TRUNCATE TABLE in DB2

Well there isn't one.  If you read this post and your platform is SQL Server or Oracle, I hope you feel very fortunate that you have

TRUNCATE TABLE <tablename>. 

It appears that in version 8, there is a callable procedure that is supplied by IBM, but it cannot be executed as a standard SQL command.  It probably not ANSI anyway.  In any event, the way of the world to do this in the past, to not incur heavy logging of a DELETE, was to use the DB2 Load utility and do a LOAD REPLACE.  This has the affect of marking the entire tablespace as emplty.  A tablespace is a set of predefined allocated space that may contain 1 or many tables.  It is very important to understand that a LOAD REPLACE will wipe out all tables in the TABLESPACE.  It is for this very reason that I traditionally will only allocate 1 table per tablespace.

I'll need to post the pain that's involved with stored procedures in DB2, but just as you can't call the TRUNCATE, you can't execute stored procedure at all, from SQL.  Then I stumbled on this link (after posting several times in DB2 Forums and getting no answer) I found this gem in the above link (written by Marina Greenstein (greenstm@us.ibm.com), Certified Consulting I/T Specialist - Data Management, IBM, Arthur V Sammartino (asamma@us.ibm.com), Certified Consulting I/T Specialist - Data Management, IBM and Knut Stolze (stolze@de.ibm.com), Information Integration Development, IBM)

UDF to call a stored procedure from a trigger or user-defined function

Another common issue encountered when migrating to DB2 is the capability of other RDBMS to call stored procedures from triggers or functions. Although this feature is available in DB2 UDB Version 8.2, earlier versions require a work-around. We show how this feature may be implemented using the version 7 or 8.1 of DB2; that is, by creating a UDF that will issue a call to a stored procedure.

OK, until I get around to writing an article the describes the PAIN involved in creating a stored procedures, please feel good about the platform you are on.  I will share the pain, and the tips to make your life easier in case you ever have to get invovled in creating DB2 Store Procedures.  But back to the article.

Here's the “Code“ to “TRUNCATE“ a table (well tablespace). it's actually called JCL (Job Control Language).

The steps that are executed are.

  1. Uncatalog any dataset names so the can be used by the job
  2. I unload the data prior to wiping it out
  3. Put the TABLESPACE in to UTILITY PENDING mode (So no one can access the tablespace while it's being worked on)
  4. LOAD The Table with the Control card using the Options LOAD DATA REPLACE...(Sample Card is at the bottom of the post).  The input Dataset used for the load is Dummied out.
  5. REPAIR The TABLESPACE
  6. Run RUNSTATS on the tablespace to update the system tables about the change to the TABLESPACEs Statistics
  7. Start the TABLESPACE and place it in Read Write Mode (RW) Mode

And that's it...pretty simple huh.

  

//REPLACE JOB (B,X,BB7200),'DB2 DBA',GROUP=AXBB72PA,          
//   NOTIFY=&SYSUID,MSGCLASS=V,COND=(4,LT),RESTART=STARTRW    
//*+JBS BIND XPDDBA1.ONLINE                                   
//UNCAT    EXEC PGM=IEXUNCAT,COND=(4,LT)                      
//SYSIN DD *                                                  
   BXBB72.DBA1.GUD000DA.UNLOAD.GUSBK011.D050428               
   BXBB72.X002548.DBA1.GUD000DA.DISC.GUSBK011                 
   BXBB72.X002548.DBA1.GUD000DA.UNLD.GUSBK011
                 
/*                                                            
//UNLOAD  EXEC PGM=IKJEFT01,REGION=6M,COND=(4,LT)             
//STEPLIB   DD DSN=AXXXA1.DB2.SDSNLOAD,DISP=SHR               
//SYSTSPRT  DD SYSOUT=*                                       
//SYSPRINT  DD SYSOUT=*                                       
//SYSUDUMP  DD DUMMY                                          
//SYSREC00  DD SPACE=(CYL,(100,25),RLSE),                     
//             UNIT=DASD,DISP=(,CATLG),LABEL=RETPD=365,       
//             DSN=BXBB72.DBA1.GUD000DA.UNLOAD.GUSBK011.D050428
//*SYSPUNCH  DD DUMMY                                         
//SYSPUNCH  DD DISP=SHR,                                            
//      DSN=BXBB72.X002548.DBA1.GUD000DA.CTLCRD(GUSBK011)           
//SYSTSIN   DD *                                                    
  DSN SYSTEM(DBA1)                                                  
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -                            
      LIB('AXXXA1.DB2.RUNLIB.LOAD') PARMS('SQL')                    
  END
                                                               
/*                                                                  
//SYSIN DD DISP=SHR,DSN=BXBB72.X002548.DBA1.GUD000DA.UNLOAD(GUSBK011)
/*                                                                  
//STARTUT EXEC PGM=IKJEFT1A,DYNAMNBR=20                             
//STEPLIB  DD DSN=AXXXA1.DB2.SDSNLOAD,DISP=SHR                      
//SYSTSPRT DD SYSOUT=*                                              
//SYSPRINT DD SYSOUT=*                                              
//SYSUDUMP DD DUMMY                                                 
//SYSIN    DD DUMMY                                                 
//SYSTSIN  DD *                                                     
 DSN SYSTEM(DBA1)                                                   
 -START DATABASE(GUD000DA) SPACENAM(GUSBK011) ACCESS(UT)
            
/*                                                       
//LOAD     EXEC PGM=DSNUTILB,REGION=4096K,               
//         PARM='DBA1,GUSBK011'                          
//STEPLIB  DD DSN=AXXXA1.DB2.SDSNLOAD,DISP=SHR           
//SYSPRINT DD SYSOUT=*                                   
//UTPRINT  DD SYSOUT=*                                   
//SYSUDUMP DD DUMMY                                      
//SORTWK01 DD SPACE=(23476,(500,200),RLSE),UNIT=SYSOUT3  
//SORTWK02 DD SPACE=(23476,(500,200),RLSE),UNIT=SYSOUT3  
//SORTWK03 DD SPACE=(23476,(500,200),RLSE),UNIT=SYSOUT3  
//SORTWK04 DD SPACE=(23476,(500,200),RLSE),UNIT=SYSOUT3  
//SYSUT1   DD DSN=&&SYSUT1,DISP=(,PASS),UNIT=SYSOUT3,    
//            SPACE=(23476,(500,200),RLSE)               
//SORTOUT  DD DSN=&&SORTOUT,DISP=(,PASS),UNIT=SYSOUT3,   
//            SPACE=(23476,(500,200),RLSE)               
//SYSERR   DD DSN=&&SYSERR,DISP=(,PASS),UNIT=SYSOUT3,    
//            SPACE=(23476,(500,200),RLSE)               
//SYSMAP   DD DSN=&&SYSMAP,DISP=(,PASS),UNIT=SYSOUT3,    
//            SPACE=(23476,(500,200),RLSE)               
//SYSDISC  DD UNIT=DASD,DISP=(MOD,CATLG,CATLG),             
//         SPACE=(CYL,(100,25),RLSE),LABEL=RETPD=365,       
//         DSN=BXBB72.X002548.DBA1.GUD000DA.DISC.WPTA       
//SYSREC00 DD DUMMY                                         
//*SYSREC00 DD DISP=SHR,                                    
//*         DSN=BXBB72.X002548.DBA1.GUD000DA.DATA.WPTA      
//*        DSN=BXBB72.X002548.DBB1.GUD000DA.DATA.REQ001     
//*        DSN=BXBB72.X002548.DBA1.GUD000DA.WPTA.D020205    
//SYSIN    DD DISP=SHR,                                     
//         DSN=BXBB72.X002548.DBA1.GUD000DA.CTLCRD(GUSBK011)
//*                                                         
//* REPAIR                                                  
//*                                                         
//REPAIR   EXEC PGM=DSNUTILB,REGION=4096K,COND=(4,LT),      
//    PARM='DBA1,GUSBK011'                                  
//STEPLIB  DD DSN=AXXXA1.DB2.SDSNLOAD,DISP=SHR              
//SYSPRINT DD SYSOUT=*                                      
//UTPRINT  DD SYSOUT=*                                      
//SYSUDUMP DD SYSOUT=*                                      
//SYSIN    DD *                                                
 REPAIR OBJECT SET TABLESPACE GUD000DA.GUSBK011 NOCOPYPEND     
//*                                                            
//**************************************************************
//* REORG                                                      
//*                                                            
//* RUNSTAT                                                    
//*                                                            
//RUNSTAT EXEC DSNUPROC,SYSTEM=DBA1,UID='GUSBK011',UTPROC=''   
//STEPLIB  DD DSN=AXXXA1.DB2.SDSNLOAD,DISP=SHR                 
//SYSPRINT DD SYSOUT=*                                         
//UTPRINT  DD SYSOUT=*                                         
//SYSUDUMP DD DUMMY                                            
//DSNUPROC.SYSIN    DD  *                                      
RUNSTATS TABLESPACE GUD000DA.GUSBK011 INDEX(ALL) SHRLEVEL CHANGE
//*                                                            
//* RESTART TABLESPACE                                         
//*                                                            
//STARTRW EXEC PGM=IKJEFT1A,DYNAMNBR=20,COND=(4,LT)            
//STEPLIB  DD DSN=AXXXA1.DB2.SDSNLOAD,DISP=SHR           
//SYSTSPRT DD SYSOUT=*                                   
//SYSPRINT DD SYSOUT=*                                   
//SYSUDUMP DD DUMMY                                      
//SYSIN    DD DUMMY                                      
//SYSTSIN  DD *                                          
 DSN SYSTEM(DBA1)                                        
 -START DATABASE(GUD000DA) SPACENAM(GUSBK011) ACCESS(RW)
 
/*                                                       


Sample Load Card

  LOAD DATA REPLACE LOG NO INDDN SYSREC00 INTO TABLE                
      AXBB72DA.WS_SERV_LOG                                          
   (                                                                
   LOGIN_ID                               POSITION(       1         )
   CHAR(                     30) ,                                  
   HOST_IP                                POSITION(      31         )
   CHAR(                     20) ,                                  
   REQ_URL                                POSITION(      51         )
   CHAR(                    100) ,                                  
   QUERY_STRING                           POSITION(     151         )
   CHAR(                    100) ,                                  
   REQ_DOMAIN                             POSITION(     251         )
   CHAR(                    100) ,                                  
   REQ_TS                                 POSITION(     351         )
   TIMESTAMP EXTERNAL(       26) ,                                  
   REQ_PROC                               POSITION(     377         )
   CHAR(                      8) ,                                  
   REQ_RS                                 POSITION(     385         )
   INTEGER                       ,                                   
   REQ_LENGTH                             POSITION(     389:     397)
   DECIMAL                       ,                                   
   SQLCODE                                POSITION(     398         )
   INTEGER                                                           
   )                                                                 

Print | posted on Thursday, April 28, 2005 11:54 AM | Filed Under [ DB2 ]

Feedback

Gravatar

# re: TRUNCATE TABLE in DB2

Whoa - that brought back some memories :-)

Have you had the fun other restore a tablespace from 1 database to another yet?
5/13/2005 4:22 AM | Wanderer
Gravatar

# re: TRUNCATE TABLE in DB2

Can you say QUIESCE?

5/13/2005 10:31 AM | Brett (Not just a Number...huh?)
Gravatar

# re: TRUNCATE TABLE in DB2

Really this was a nice article. It gave me a clear undertstanding how a data can be unloaded and reloaded from db2 tables.

Thanks
5/22/2005 3:59 AM | Mohamed Qhadir
Gravatar

# Your Welcome and Thanks

As of this post, it seems that this article has been viewed about 5,000 times.

Thanks for the feedback.

5/23/2005 9:42 AM | Brett (Not just a Number...huh?)
Gravatar

# re: TRUNCATE TABLE in DB2

DB2's IMPORT functionality provides the means to achieve the same functionality if the REPLACE INTO clause is used, and an empty file is designated as the data source. In that case, all rows of the table are quickly removed using only a single log record, before the new data is imported from the given file. With an empty file, nothing gets imported so that the table is left truncated at the end of the operation.
11/28/2005 2:48 PM | IRO
Gravatar

# re: TRUNCATE TABLE in DB2

VERY USEFUL DOC
8/28/2006 11:52 PM | BARATHI
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET