ALP,BMC,ZOS] LOG MASTER 테이블 조회 SAMPLE

SELECT * FROM (
SELECT A.USERID,A.WORKIDNAME,A.RUNSEQNUM,A.HANDLE,HEX(A.URID) AS UI
,TBJ.MINUI
,CASE WHEN HEX(A.URID) < TBJ.MINUI THEN 1
ELSE 2 END AS CHKFLG
FROM BBIALP.ALPURID A,
(SELECT B.USERID,B.WORKIDNAME,B.RUNSEQNUM,B.HANDLE
,MIN(HEX(B.URIDLRSN)) AS MINUI FROM BBIALP.ALPURID B
WHERE B.RECORDTYPE = 2
AND B.USERID = 'LOGMSTRA'
AND B.WORKIDNAME LIKE 'LLOG000%'
GROUP BY B.USERID,B.WORKIDNAME,B.RUNSEQNUM,B.HANDLE) TBJ
WHERE A.RECORDTYPE = 1
AND A.USERID = TBJ.USERID
AND A.WORKIDNAME = TBJ.WORKIDNAME
AND A.RUNSEQNUM = TBJ.RUNSEQNUM
AND A.HANDLE = TBJ.HANDLE
) X
WHERE X.CHKFLG = 2
AND X.RUNSEQNUM >= 2599

DB2,ZOS] IFNULL, NULL 데이터 처리 쿼리문

설명 : IFNULL 기능을 사용해서 다른 값으로 대체 함.

SELECT I.JOB_NAME ,I.JOB_OWNER, I.START_DATE, I.START_TIME
,I.ELAPSED_TIME, I.CPU_TIME, I.PROC_STEP, I.JCL_STEP
,I.PGM_NAME, J.RESULT_GB
,IFNULL(J.RESULT_CNTI,0),IFNULL(J.RESULT_CNT,0)
FROM CDBD2D20.TD2I0 I LEFT JOIN CDBD2D20.TD2J0 J
ON I.JOB_NAME = J.JOB_NAME
AND I.JOB_OWNER = J.JOB_OWNER
AND I.JOB_DATE = J.JOB_DATE
AND I.JOB_NUMBER = J.JOB_NUMBER
AND I.STEP_SEQ = J.STEP_SEQ
WHERE I.JOB_DATE >= '2008171'
AND I.JOB_DATE <= '2008172'
ORDER BY I.JOB_NAME, I.JOB_OWNER, I.JOB_DATE
,I.STEP_SEQ
WITH UR;

excel] 문자 탐색 - vlookup #1

=IF(ISNA(VLOOKUP(C2,plch!A:F,6,FALSE)),0,VLOOKUP(C2,plch!A:F,6,FALSE))

ftp로 dsn list 출력 하기 - log 파일 지정

>> dataset-list.bat
ftp -s:dataset-list_plcB.src 10.10.11.11 > DSLIST_PLCB.out


>> dataset-list_plcB.src
bskim
JUN2008
dir 'PLCB.MIGUF.*'
bye

ICETOOL] record count jcl

//ICETOOL EXEC PGM=ICETOOL,REGION=0M
//DATA1 DD DISP=SHR,DSN=TLCH.MIGUF.KBSLIB.DATA1
//*ORTWK01 DD UNIT=SYSDA,SPACE=(640,3),AVGREC=M
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLIN DD *
COUNT FROM(DATA1)
/*

REXX,예제] 일자계산

/* REXX */
START_DATE = '2008-05-19'
START_TIME = '00:05:59'
END_DATE = '2008-05-19'
END_TIME = '20:19:20'

ELAPSE_TIME = GET_ETM(START_DATE,START_TIME,END_DATE,END_TIME)
SAY '********* ' ELAPSE_TIME '*********'
EXIT

/* GETTING ELAPSE TIME */
GET_ETM:
ARG D1,T1,D2,T2
/*SAY D1 T1 D2 T2 */

D1 = SUBSTR(D1,1,4) || SUBSTR(D1,6,2) || SUBSTR(D1,9,2)
D2 = SUBSTR(D2,1,4) || SUBSTR(D2,6,2) || SUBSTR(D2,9,2)

D1_JUL = DATE('B',DATE(,D1,'S'))
D2_JUL = DATE('B',DATE(,D2,'S'))

T1_SEC = SUBSTR(T1,1,2) * 60 * 60 ,
+ SUBSTR(T1,4,2) * 60 ,
+ SUBSTR(T1,7,2)

T2_SEC = SUBSTR(T2,1,2) * 60 * 60 ,
+ SUBSTR(T2,4,2) * 60 ,
+ SUBSTR(T2,7,2) ,
+ ( ( D2_JUL - D1_JUL ) * 60 * 60 * 24 ) /* DAY */

GAP = ( T2_SEC - T1_SEC )

E_HH = TRANSLATE(FORMAT(GAP%(60*60),2),'0',' ')
E_MM = TRANSLATE(FORMAT((GAP-(E_HH*60*60))%60,2),'0',' ')
E_SS = TRANSLATE(FORMAT(GAP//60,2),'0',' ')
E_TM = E_HH':'E_MM':'E_SS

RETURN E_TM

ZOS] sms rule 변경 후 적용 명령어

acds switch 명령어
/setsms acds(SYS1.SMS.PROD.ACDS)


scds 적용 및 activate
/setsms scds(SYS1.SMS.PROD.SCDS)

ZOS] multi volume data set catalog add

//PSJTSO1V JOB A,A,CLASS=K,MSGCLASS=X
//*------------------------------------------------------------
//* VSAM RECATALOG ( CLUSTER & AIX ) --
//*------------------------------------------------------------
//STEP1 EXEC PGM=IDCAMS,REGION=512K
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEFINE NONVSAM(NAME(SD.NISPLAN.UNLOAD) -
VOLUMES(RB0052,RB0053) -
DEVICETYPES(3390)) -
CATALOG(CATALOG.UC.SAM)

ZOS,BMC] ADU share level change option 사용 case #2

//BMC@UDMG JOB CLASS=W,MSGCLASS=X,NOTIFY=&SYSUID
//* UNLOAD
//UNLOAD EXEC PGM=ADUUMAIN,REGION=0M,
// PARM='DSNA,LCFB01TS,NEW,,MSGLEVEL(1)'
//STEPLIB DD DISP=SHR,DSN=BMCP.DB2.LOAD
//SYSREC01 DD DSN=SD.DATA.D071206.LCFB01T.M200711,
// DISP=(NEW,CATLG),UNIT=3390,
// SPACE=(CYL,(100,200),RLSE),
// VOL=SER=RB0045
//SORTWK01 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB10
//SORTWK02 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB11
//SORTWK03 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB12
//SORTWK04 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB13
//SORTWK05 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB14
//SORTWK06 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB15
//SORTWK07 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB16
//SORTWK08 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB17
//SORTWK09 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB18
//SORTWK10 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB19
//*YSCNTL DD DSN=SD.DATA.D071206.LCFB01T.PUNCH,
//* UNIT=3390,SPACE=(TRK,(15,15)),
//* DISP=(NEW,CATLG),VOL=SER=KB1319
//UTPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
UNLOAD
SHRLEVEL CHANGE
UNLOADDN(SYSREC01)
SELECT * FROM KBDB.LCFB01T
WHERE LCFB01_FIN_YM = '200711'
;
/*

ZOS,BMC] ADU share level change option 사용 case #1

//BMC@UD03 JOB CLASS=W,MSGCLASS=X,NOTIFY=&SYSUID
//* UNLOAD
//UNLOAD EXEC PGM=ADUUMAIN,REGION=0M,
// PARM='DSNA,LCFB01TS,NEW,,MSGLEVEL(1)'
//STEPLIB DD DISP=SHR,DSN=BMCP.DB2.LOAD
//VSAMDD DD DISP=SHR,DSN=PDB2.DSNDBD.LCDB00.LCFB01S.I0001.A003
//SYSREC01 DD DSN=SD.DATA.D071206.LCFB01T.PART03,
// DISP=(NEW,CATLG),UNIT=3390,
// SPACE=(CYL,(2000,200),RLSE),
// VOL=SER=(RB0021,RB0046)
//SORTWK01 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB10
//SORTWK02 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB11
//SORTWK03 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB12
//SORTWK04 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB13
//SORTWK05 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB14
//SORTWK06 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB15
//SORTWK07 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB16
//SORTWK08 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB17
//SORTWK09 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB18
//SORTWK10 DD UNIT=3390,SPACE=(CYL,(2000,300)),VOL=SER=UADB19
//*YSCNTL DD DSN=SD.DATA.D071206.LCFB01T.PUNCH,
//* UNIT=3390,SPACE=(TRK,(15,15)),
//* DISP=(NEW,CATLG),VOL=SER=KB1319
//UTPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
UNLOAD
DIRECT YES SHRLEVEL CHANGE
INFILE VSAMDD
UNLOADDN(SYSREC01)
SELECT * FROM KBDB.LCFB01T OPTIONS (PART 3);
/*