|
NEW!!! TOOOO Many results in general search?!! Try this customized search engine for searching online books
|
|
sp_depends for DB2
Well, there really isn't anything that I know of that is like sp_depends for DB2 z/OS Version 7.2. Hopefully V8 will alot more features...but for Now you have to interogate the catalog. So this is how you do it....
SELECT DISTINCT NAME,DNAME,BNAME
FROM SYSIBM.SYSPACKDEP D
INNER JOIN SYSIBM.SYSPACKSTMT S
ON D.DCOLLID = S.COLLID AND D.DNAME = S.NAME
AND D.DCONTOKEN = S.CONTOKEN
WHERE BQUALIFIER = 'AXHRSPDA'
AND BNAME IN('POSITION_TREE')
ORDER BY NAME,DNAME,BNAME
;
And this new editor for posting is pretty neat...lots of features...like background color...and for anyone not familiar with the mainframe...that's what I get to look at all day..until I get another SQL Server project
 |
How to FTP Files to OS/390
The best way I've found to transfer is to use ftp with a script file. Now with Cyberfusion being implemented, I unfortunately have to abandon this simple methd for secure file transfers. The thing that really gets me though, is this company policy being enforced, evenb though everything is interbal and behind a firewall. And Cyberfusion iis a pain in the neck to setup...and it seems the "Service" company doesn't have a good handle on it...and they have to set up the profiles. In any case, create a script file like:
open <machine name>
<UserID>
<Password>
quote site filetype=seq CYlinders Primary=50 Secondary=100 lrecl=5000 recfm=FB
put W:\data\HRSecDB2\Data\<pc_filename> 'SMF.DSN.'
quit
Then create abatch file so it can be executed from a command prompt, xp_cmdshell...whatever and redirect the output to a log file so you can confirm the transmission. When I do this from SQL Server, I load the log file table and interogate the transmission and then take appropriate action.
ftp -s:W:\data\HRSecDB2\ftp_HRSecDB2.ftp > W:\data\HRSecDB2\ftp_HRSecDB2.log
There now I have a place I can find this stuff in case my laptop fries again...I hope graz has backups  |
|
|
Generate History Tables in DB2 From the System Catalog
I'm currently setting up a process to audit changes to the database in DB2 OS/390. I have done this in the past with SQL Server (which was easier) but I know have to do it for this platform. Basically, for an UPDATE or a DELETE DML operation a trigger will fire and insert the rows into this table. The following SQL will genertae table creates for each table in a database. Just change the Creator for which ever database you want to audit.
SET CURRENT SQLID = 'BXHRSPDA';
SELECT SQL FROM (
SELECT NAME AS TABLE_NAME,1 AS ROWORDER, 1 AS COLORDER,
'CREATE TABLE H_'||SUBSTR(NAME,1,16)||' (' AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT TBNAME AS TABLE_NAME, 2 AS ROWORDER, 1 AS COLORDER,
' '||NAME||SPACE(19-LENGTH(NAME))
||CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END
||SPACE(20-LENGTH(
CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END))
||CASE WHEN NULLS = 'Y' THEN ' NULL'
WHEN NULLS = 'N' THEN ' NOT NULL'
ELSE '"'||COALESCE(NULLS,' ')||'"'
END
AS SQL
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'AXHRSPDA'
AND COLNO = 1
UNION ALL
SELECT TBNAME AS TABLE_NAME, 2 AS ROWORDER, COLNO AS COLORDER,
' , '||NAME||SPACE(19-LENGTH(NAME))
||CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END
||SPACE(20-LENGTH(
CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END))
||CASE WHEN NULLS = 'Y' THEN ' NULL'
WHEN NULLS = 'N' THEN ' NOT NULL'
ELSE '"'||COALESCE(NULLS,' ')||'"'
END
AS SQL
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'AXHRSPDA'
AND COLNO <> 1
UNION ALL
SELECT NAME AS TABLE_NAME,3 AS ROWORDER, 1 AS COLORDER,
' , HIST_ADD_TS TIMESTAMP NOT NULL DEFAULT'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT NAME AS TABLE_NAME,4 AS ROWORDER, 1 AS COLORDER,
' , HIST_ADD_BY CHAR(12) NOT NULL DEFAULT'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT NAME AS TABLE_NAME,5 AS ROWORDER, 1 AS COLORDER,
' , HIST_ADD_TYPE CHAR(1) NOT NULL DEFAULT'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT NAME AS TABLE_NAME, 99 AS ROWORDER, 1 AS COLORDER,
');'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
) AS XXX
ORDER BY TABLE_NAME, ROWORDER, COLORDER
;
 |
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.
-
Uncatalog any dataset names so the can be used by the job
-
I unload the data prior to wiping it out
-
Put the TABLESPACE in to UTILITY PENDING mode (So no one can access the tablespace while it's being worked on)
-
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.
-
REPAIR The TABLESPACE
-
Run RUNSTATS on the tablespace to update the system tables about the change to the TABLESPACEs Statistics
-
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
)
 |
DB2 SET UPDATE
No cursors please! DB2 syntax varies from SQL Server.
Just because it took me an hour to find it....I wish the syntax would be the same...but they're not
UPDATE ASSESS_AUDIT A1
SET FIRST_NAME = (SELECT SUB_FIRST_NAME
FROM ORG_TREE O1
WHERE A1.EMPL_ID = O1.EMPL_ID)
WHERE FIRST_NAME = ' '
AND EXISTS (SELECT * FROM ORG_TREE O2
WHERE A1.EMPL_ID = O2.EMPL_ID);
Another example
UPDATE SS_FAMILY_TREE_MAP SFTM
SET ORG_NAME = ( SELECT SUBSTR(SOR.SS_ORG_NAME,1,25)
FROM SS_ORG_REL SOR
WHERE SFTM.SS_ORG_CD = SOR.SS_ORG_CD
AND SFTM.SS_STRUCTURE_NAME = SOR.STRUCTURE_NAME)
WHERE ORG_NAME IS NULL OR ORG_NAME = ' '
;
 |
Calculate the most effecient Storage size based on the catalog
The Following code is used to identify reccomended allocations, whether it be in Cylinders or tracks, and how much to allocate for a tablespace.
SELECT NAME
, NACTIVE*PGSIZE AS KILOBYTES
, PGSIZE AS PAGE_SIZE
, NACTIVE AS PAGES
, DECIMAL((NACTIVE/12.00),15,2) AS TRACKS
, DECIMAL(((NACTIVE/12.00)/15),15,2) AS CYLINDERS
, CASE
WHEN DECIMAL(((NACTIVE/12.00)/15),15,2) > 1 THEN
'CYLINDERS' ELSE
'TRACKS '
END AS RECMNED_ALLOC
, CASE
WHEN INTEGER(NACTIVE/12.00) <> NACTIVE/12.00 THEN
(INTEGER(NACTIVE/12)+1)*48 ELSE
INTEGER(NACTIVE/12) *48
END AS FULL_TRACK_BYTES
, CASE
WHEN INTEGER(((NACTIVE/12.00)/15.00)) <>
(NACTIVE/12.00)/15.00 THEN
(INTEGER((NACTIVE/12.00)/15.00)+1)*720 ELSE
INTEGER((NACTIVE/12.00)/15.00) *720
END AS FULL_CYLDR_BYTES
FROM SYSIBM.SYSTABLESPACE
WHERE CREATOR = 'BX89ACDA'
AND DBNAME = 'SLD000DA'
ORDER BY 1;
 |
|
|
|
ONLINE FORUM
|
|
|
