NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Tue Dec 05, 2006 6:08 pm Post subject: conditional drop db2 |
 |
|
Does anyone have an example of a DB2 Drop that executes only if the object exists?
The SQL sever team uses code like this
| Code: |
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DOC_HISTORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DOC_HISTORY]
GO
|
_________________
Thanks,
NASCAR9 |
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Dec 07, 2006 10:11 am Post subject: |
|
|
Nascar9,
Try this. We will create dynamic DDL command check if the table existed in sysibm.systables in step0100. In step0200 we use that dynamic control card to drop or do nothing in step0200.
Step0100 returns 1 row if there is no object found with the name LIKE '%DOC_HISTORY%' or it will return 0 rows if there is an object named 'DOC_HISTORY'.
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(xxx)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('DB2P.RUNLIB.LOAD')
/*
//SYSREC00 DD DSN=dynamic output dml command dsn,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(TRK,(1,1),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CHAR('DROP TABLE DBO.DOC_HISTORY;',80)
FROM SYSIBM.SYSDUMMY1 A
WHERE NOT EXISTS (SELECT NAME
FROM SYSIBM.SYSTABLES B
WHERE NAME LIKE '%DOC_HISTORY%'
AND A.IBMREQD = A.IBMREQD)
FETCH FIRST 1 ROW ONLY;
/*
//STEP0200 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM(xxxx)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP2) -
LIB('DB2P.RUNLIB.LOAD')
END
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD DSN=dynamic output dml command dsn,
// DISP=SHR
/*
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
