데이터베이스 유틸리티

//JOB01   JOB  CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//JOBLIB  DD   DSN=SYS1.COBLIB,DISP=SHR
//STEP    EXEC PGM=DFSRRC00,
//             PARM='DLI,DSNMTV01,HOSPPSB,,0000,,0,,N,O,T'
//SYSOUT  DD   SYSOUT=*
//DDITV02 DD   DSN=DSNMTV01.PARMLIB(SAMPLE),DISP=SHR
//*

//JOB01   JOB  CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//JOBLIB  DD   DSN=SYS1.COBLIB,DISP=SHR
//STEP    EXEC PGM=DFSRRC00,
//             PARM='DLI,DSNMTV01,HOSPPSB,,0000,,0,,N,O,T'
//SYSOUT  DD   SYSOUT=*
//DDITV02 DD   DSN=DSNMTV01.PARMLIB(SAMPLE),DISP=SHR
//*

DB2Q,SYS1,DSNMIN10,,A,-,MP13J120,TESTPGM,TESTPGM

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
 DSN SYSTEM(DBPA)
 RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNTEP2  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DBPA)
 RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
  SELECT * FROM DSN8810.PROJ;
/*

###### DSNTEP2 DB INFO : USERNAME=tibero,PASSWORD=****,DATABASE=tb_rbtest
##### time check - begin #####
PAGE    1
***INPUT STATEMENT:
    SELECT *
    FROM DSN8810.PROJ;
       +-----------------------------------+
       ! COLONE               ! COLTWO     !
       +-----------------------------------+
     1_! AAA                  !        392 !
     2_! AAA                  !        105 !
       +-----------------------------------+
SUCCESSFUL RETRIEVAL OF          2 ROW(S)
****************************************** Bottom of Data ******************************************
##### time check - end : elapsed time(sec) = 0.000983 #####

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNTEP2  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DBPA)
 RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
  UPDATE TEST_TABLE SET COLONE = 'TESTVALUE'
/*

###### DSNTEP2 DB INFO : USERNAME=tibero,PASSWORD=****,DATABASE=tb_rbtest
##### time check - begin #####
PAGE    1
***INPUT STATEMENT:
    UPDATE TEST_TABLE
    SET COLONE = 'TESTVALUE' ;
NUMBER OF ROWS AFFECTED 2
****************************************** Bottom of Data ******************************************
##### time check - end : elapsed time(sec) = 0.000350 #####

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
 RUN PROGRAM(DSNTIAD) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNTEP2  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 RUN PROGRAM(DSNTIAD) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
INSERT INTO DSNTAID_GROUP(GROUPNAME, OWNER, SUPGROUP, SUBGRPS, MODEL,
        DATA, CREATION, FLAGS)
VALUES('TESTGRP3', 'JUNG', NULL, NULL, NULL, NULL, '20101005', 0);
DELETE FROM DSNTAID_GROUP WHERE GROUPNAME = 'TESTGRP2';
UPDATE DSNTAID_GROUP SET OWNER = 'TEST01' WHERE GROUPNAME = 'TESTGRP1';
/*

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD') PARM('SQL')
//*

//JOB01   JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//STEP    EXEC PGM=IKJEFT01
//SYSTSIN DD *
RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD') PARM('SQL')
//*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//         UNIT=SYSDA,SPACE=(32760,(1000,500)),
//         DISP=(NEW,CATLG),VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//         UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(NEW,CATLG),
//         VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
 SELECT * FROM DSN8810.PROJ
/*

//JOB02    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//UNLOAD   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
 RUN PROGRAM(DSNTIAUL) PLAN(DSNTEP81) LIB('DSN810.RUNLIB.LOAD')
//*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//         UNIT=SYSDA,SPACE=(32760,(1000,500)),
//         DISP=(NEW,CATLG),VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//         UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(NEW,CATLG),
//         VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN    DD *
 DSN8810.PROJ
/*

//STEP EXEC PGM=DSNUTILB[,PARM='system,[uid],[utproc]']

LOAD DATA
    INDDN ddname1
    [REPLACE]
    INTO TABLE table_name [field_name [POSITION ( field_position : field_length )]]
    [DELIMITED [COLDEL del_char1][CHARDEL del_char2][DECPT del_char3]]
    [WORKDDN (ddname2, ddname3)]
    [ENFORCE NO]
    [DISCARDS discard_number]
    [DISCARDDDN discard_ddname]
    [ERRDDN SYSERR]
    [MAPDDN SNAP]

UNLOAD DATA
    UNLDDN ddname1 | (ddname1)
    PUNCHDDN  ddname2
    FROM TABLE (table_name [field_name field_type(field_length) [CLOBF template_ddname] [EXTERNAL] ])
    [WHEN (where_condition)]
    [DELIMITED [COLDEL del_char1][CHARDEL del_char2][DECPT del_char3]]

QUIESCE TABLESPACE tablespace_name
        ...

TEMPLATE ddname DSN 'dsname' DSNTYPE(dsntype) UNIT(unit) SPACE(primary,secondary) [CYL]

//JOB01    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNLOAD  EXEC PGM=DSNUTILB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC   DD DSN=DSN8UNLD.SYSREC00,DISP=(SHR,KEEP)
//SYSIN    DD *
LOAD DATA
 INDDN SYSREC
 REPLACE
 LOG NO
 SORTDEVT DISK
 INTO TABLE
 TEST_TABLE
 (
 PO_NBR POSITION ( 1: 11 ) CHAR
 )
 WORKDDN (SYSUT101,SORTOUT)
 ENFORCE NO
 DISCARDDN SYSDISC
 ERRDDN SYSERR
 MAPDDN SMAP
/*
//

//ULDT1    JOB
//STP1     EXEC PGM=DSNUTILB,PARM='DBTW,A,B'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD DSN=ULDTEST.ULD,DISP=(NEW,CATLG,DELETE)
//SYSIN    DD *
TEMPLATE LOBFRW DSN 'SYS1.TSTLIB'
                DSNTYPE(PDS) UNIT(3390)
                SPACE(50,250) CYL
UNLOAD DATA FROM TABLE DATA_UNLOAD HEADER NONE
  (
   NUMBER     CHAR(10)
  ,QUOTE_ID       DECIMAL
  ,CLASS_ID       CHAR(08)
  ,DATA_XML     VARCHAR CLOBF LOBFRW
  ,ADDED_DATE     TIMESTAMP EXTERNAL(26)
  ,ADDED_LOGON    CHAR(15)
  ,REVISED_DATE   TIMESTAMP EXTERNAL(26)
  ,REVISED_LOGON  CHAR(15)
  )
  WHEN (ADDED_DATE >= (CURRENT_TIMESTAMP - 1 YEAR))
       UNLDDN     SYSREC00
       PUNCHDDN   SYSPUNCH
       DELIMITED  COLDEL X'09'
       SHRLEVEL CHANGE
/*

//JOB01    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//RUNLOAD  EXEC PGM=DSNUTILB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
 QUIESCE TABLESPACE DEFSPACE
/*
//

//STEP EXEC PGM=INZUTILB

//SYSIN DD *
 SELECDT * FROM EMPLOYEE
/*

//JOB01    JOB CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1)
//UNLOAD   EXEC PGM=INZUTILB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC   DD DSN=TEST.EMPLOYEE,DISP=(NEW,CATLG,DELETE)
//SYSIN    DD *
 SELECT * FROM EMPLOYEE
/*
//