DSNTEJ2U
THIS JCL PREPARES THE FOLLOWING Db2 USER-DEFINED FUNCTIONS (UDF'S) AND A DRIVER PROGRAM TO INVOKE THEM.
//*********************************************************************
//* NAME = DSNTEJ2U
//*
//* DESCRIPTIVE NAME = DB2 SAMPLE APPLICATION
//* PHASE 2
//* USER DEFINED FUNCTIONS (C/C++)
//*
//* Licensed Materials - Property of IBM
//* 5650-DB2
//* (C) COPYRIGHT 1982, 2016 IBM Corp. All Rights Reserved.
//*
//* STATUS = Version 12
//*
//* FUNCTION = THIS JCL PREPARES THE FOLLOWING DB2 USER-DEFINED
//* FUNCTIONS (UDF'S) AND A DRIVER PROGRAM TO INVOKE THEM.
//*
//* NOTES = ENSURE THAT LINE NUMBER SEQUENCING IS SET 'ON' IF
//* THIS JOB IS SUBMITTED FROM AN ISPF EDIT SESSION
//*
//* THIS JOB IS RUN AFTER PHASE 1.
//*
//* CHANGE ACTIVITY =
//* 10/16/2013 Don't use prelinker by default PI13612 DM1812
//* 08/18/2014 Single-phase migration s21938_inst1 s21938
//*
//*********************************************************************
//*
//JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR
// DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
// DD DSN=CEE.V!R!M!.SCEERUN,DISP=SHR
// DD DSN=DSN!!0.RUNLIB.LOAD,DISP=SHR
//*
//* STEP 1: DROP ANY EXISTING DB2 SAMPLE UDF'S
//*
//PH02US01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) -
PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARM('RC0')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
DROP SPECIFIC FUNCTION DSN8.DSN8DUCDDVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUCDVVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUADV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUCTTVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUCTVVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUATV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUCYFV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUCYFVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8EUDND RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8EUDNV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8EUMND RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8EUMNV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTINV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTINVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTINVVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTISV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTISVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTISVVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTILV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTILVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUTILVVV RESTRICT;
DROP SPECIFIC FUNCTION DSN8.DSN8DUWFV RESTRICT;
//*
//* STEP 2: DEFINE SAMPLE UDF'S TO DB2
//*
//PH02US02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) -
PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE FUNCTION
DSN8.ALTDATE(
VARCHAR(13) CCSID EBCDIC )
RETURNS
VARCHAR(17) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUADV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUAD
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.ALTDATE(
VARCHAR(17) CCSID EBCDIC,
VARCHAR(13) CCSID EBCDIC,
VARCHAR(13) CCSID EBCDIC )
RETURNS
VARCHAR(17) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUCDVVV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUCD
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.ALTDATE(
DATE,
VARCHAR(13) CCSID EBCDIC,
VARCHAR(13) CCSID EBCDIC )
RETURNS
VARCHAR(17) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUCDDVV
SOURCE SPECIFIC DSN8.DSN8DUCDVVV;
CREATE FUNCTION
DSN8.ALTTIME(
VARCHAR(14) CCSID EBCDIC )
RETURNS
VARCHAR(11) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUATV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUAT
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.ALTTIME(
VARCHAR(11) CCSID EBCDIC,
VARCHAR(14) CCSID EBCDIC,
VARCHAR(14) CCSID EBCDIC )
RETURNS
VARCHAR(11) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUCTVVV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUCT
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.ALTTIME(
TIME,
VARCHAR(14) CCSID EBCDIC,
VARCHAR(14) CCSID EBCDIC )
RETURNS
VARCHAR(11) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUCTTVV
SOURCE SPECIFIC DSN8.DSN8DUCTVVV;
CREATE FUNCTION
DSN8.CURRENCY(
FLOAT,
VARCHAR(2) CCSID EBCDIC )
RETURNS
VARCHAR(19) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUCYFV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUCY
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.CURRENCY(
FLOAT,
VARCHAR(2) CCSID EBCDIC,
VARCHAR(5) CCSID EBCDIC )
RETURNS
VARCHAR(19) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUCYFVV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUCY
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.DAYNAME(
VARCHAR(10) CCSID EBCDIC )
RETURNS
VARCHAR(9) CCSID EBCDIC
SPECIFIC DSN8.DSN8EUDNV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8EUDN
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.DAYNAME(
DATE )
RETURNS
VARCHAR(9) CCSID EBCDIC
SPECIFIC DSN8.DSN8EUDND
SOURCE SPECIFIC DSN8.DSN8EUDNV;
CREATE FUNCTION
DSN8.MONTHNAME(
VARCHAR(10) CCSID EBCDIC )
RETURNS
VARCHAR(9) CCSID EBCDIC
SPECIFIC DSN8.DSN8EUMNV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8EUMN
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.MONTHNAME(
DATE )
RETURNS
VARCHAR(9) CCSID EBCDIC
SPECIFIC DSN8.DSN8EUMND
SOURCE SPECIFIC DSN8.DSN8EUMNV;
CREATE FUNCTION
DSN8.TABLE_NAME(
VARCHAR(18) CCSID EBCDIC )
RETURNS
VARCHAR(18) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTINV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_NAME(
VARCHAR(18) CCSID EBCDIC,
VARCHAR(8) CCSID EBCDIC )
RETURNS
VARCHAR(18) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTINVV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_NAME(
VARCHAR(18) CCSID EBCDIC,
VARCHAR(8) CCSID EBCDIC,
VARCHAR(16) CCSID EBCDIC )
RETURNS
VARCHAR(18) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTINVVV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_SCHEMA(
VARCHAR(18) CCSID EBCDIC )
RETURNS
VARCHAR(8) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTISV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_SCHEMA(
VARCHAR(18) CCSID EBCDIC,
VARCHAR(8) CCSID EBCDIC )
RETURNS
VARCHAR(8) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTISVV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_SCHEMA(
VARCHAR(18) CCSID EBCDIC,
VARCHAR(8) CCSID EBCDIC,
VARCHAR(16) CCSID EBCDIC )
RETURNS
VARCHAR(8) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTISVVV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_LOCATION(
VARCHAR(18) CCSID EBCDIC )
RETURNS
VARCHAR(16) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTILV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_LOCATION(
VARCHAR(18) CCSID EBCDIC,
VARCHAR(8) CCSID EBCDIC )
RETURNS
VARCHAR(16) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTILVV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.TABLE_LOCATION(
VARCHAR(18) CCSID EBCDIC,
VARCHAR(8) CCSID EBCDIC,
VARCHAR(16) CCSID EBCDIC )
RETURNS
VARCHAR(16) CCSID EBCDIC
SPECIFIC DSN8.DSN8DUTILVVV
LANGUAGE C
DETERMINISTIC
READS SQL DATA
EXTERNAL NAME DSN8DUTI
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
NO SCRATCHPAD
NO FINAL CALL
ALLOW PARALLEL
COLLID DSN8DU!!
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE MAIN
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
CREATE FUNCTION
DSN8.WEATHER(
VARCHAR(44) CCSID EBCDIC )
RETURNS
TABLE(
CITY VARCHAR(30) CCSID EBCDIC,
TEMP_IN_F INTEGER,
HUMIDITY INTEGER,
WIND VARCHAR(5) CCSID EBCDIC,
WIND_VELOCITY INTEGER,
BAROMETER FLOAT,
FORECAST VARCHAR(25) CCSID EBCDIC )
SPECIFIC DSN8.DSN8DUWFV
LANGUAGE C
DETERMINISTIC
NO SQL
EXTERNAL NAME DSN8DUWF
PARAMETER STYLE DB2SQL
NULL CALL
NO EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL
NO COLLID
ASUTIME LIMIT 10
STAY RESIDENT NO
PROGRAM TYPE SUB
WLM ENVIRONMENT WLMENV
SECURITY DB2
NO DBINFO;
GRANT EXECUTE ON SPECIFIC FUNCTION DSN8.DSN8DUADV,
DSN8.DSN8DUCDVVV,
DSN8.DSN8DUCDDVV,
DSN8.DSN8DUATV,
DSN8.DSN8DUCTVVV,
DSN8.DSN8DUCTTVV,
DSN8.DSN8DUCYFV,
DSN8.DSN8DUCYFVV,
DSN8.DSN8EUDNV,
DSN8.DSN8EUDND,
DSN8.DSN8EUMNV,
DSN8.DSN8EUMND,
DSN8.DSN8DUTINV,
DSN8.DSN8DUTINVV,
DSN8.DSN8DUTINVVV,
DSN8.DSN8DUTISV,
DSN8.DSN8DUTISVV,
DSN8.DSN8DUTISVVV,
DSN8.DSN8DUTILV,
DSN8.DSN8DUTILVV,
DSN8.DSN8DUTILVVV,
DSN8.DSN8DUWFV
TO PUBLIC;
//*
//* STEP 3: PREPARE EXTERNAL FOR CURRENT DATE ALTDATE UDF
//*
//PH02US03 EXEC DSNHC,MEM=DSN8DUAD,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUAD),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUAD),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUAD),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUAD(R)
//*
//* STEP 4: PREPARE EXTERNAL FOR GIVEN DATE ALTDATE UDF
//*
//PH02US04 EXEC DSNHC,MEM=DSN8DUCD,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUCD),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUCD),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUCD),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUCD(R)
//*
//* STEP 5: PREPARE EXTERNAL FOR CURRENT TIME ALTTIME UDF
//*
//PH02US05 EXEC DSNHC,MEM=DSN8DUAT,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUAT),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUAT),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUAT),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUAT(R)
//*
//* STEP 6: PREPARE EXTERNAL FOR GIVEN TIME ALTTIME UDF
//*
//PH02US06 EXEC DSNHC,MEM=DSN8DUCT,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUCT),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUCT),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUCT),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUCT(R)
//*
//* STEP 7: PREPARE EXTERNAL FOR CURRENCY UDF
//*
//PH02US07 EXEC DSNHC,MEM=DSN8DUCY,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUCY),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUCY),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUCY),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUCY(R)
//*
//* STEP 8: PREPARE EXTERNAL FOR DAYNAME UDF
//*
//PH02US08 EXEC DSNHCPP,MEM=DSN8EUDN,COND=(4,LT),
// PARM.PC=('HOST(CPP),CCSID(1047),MARGINS(1,80),STDSQL(NO)',
// SOURCE,XREF),
// PARM.CP=('/CXX SOURCE XREF OPTFILE(DD:CCOPTS)',
// 'LANGLVL(EXTENDED)'),
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8EUDN),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8EUDN),
// DISP=SHR
//CP.CCOPTS DD DSN=SYS1.PROCLIB(DSNHCPPS),DISP=SHR
//CP.USERLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8EUDN),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8EUDN(R)
//*
//* STEP 9: PREPARE EXTERNAL FOR MONTHNAME UDF
//*
//PH02US09 EXEC DSNHCPP,MEM=DSN8EUMN,COND=(4,LT),
// PARM.PC=('HOST(CPP),CCSID(1047),MARGINS(1,80),STDSQL(NO)',
// SOURCE,XREF),
// PARM.CP=('/CXX SOURCE XREF OPTFILE(DD:CCOPTS)',
// 'LANGLVL(EXTENDED)'),
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8EUMN),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8EUMN),
// DISP=SHR
//CP.CCOPTS DD DSN=SYS1.PROCLIB(DSNHCPPS),DISP=SHR
//CP.USERLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8EUMN),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8EUMN(R)
//*
//* STEP 10: PREPARE EXTERNAL FOR TABLE_NAME, TABLE_SCHEMA,
//* AND TABLE_LOCATION UDF'S
//*
//PH02US10 EXEC DSNHC,MEM=DSN8DUTI,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUTI),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUTI),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUTI),
// DISP=SHR
//LKED.IGNORE DD *
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUTI(R)
//*
//* STEP 11: BIND PACKAGE FOR TABLE_NAME, TABLE_SCHEMA, AND
//* TABLE_LOCATION UDF'S
//*
//PH02US11 EXEC PGM=IKJEFT01,COND=(4,LT)
//DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//CEEDUMP DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSIN DD *
//SYSTSIN DD *
DSN SYSTEM(DSN)
BIND PACKAGE (DSN8DU!!) MEMBER(DSN8DUTI) APPLCOMPAT(V!!R1) +
ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
END
//*
//* STEP 12: EXERCISE THE SAMPLE UDF'S
//*
//PH02US12 EXEC PGM=IKJEFT01,COND=(4,LT),DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP!!) -
LIB('DSN!!0.RUNLIB.LOAD') PARMS('/ALIGN(MID)')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
// DD DSN=DSN!!0.SDSNSAMP(DSNTESU),
// DISP=SHR
//*
//* STEP 13: PREPARE EXTERNAL FOR WEATHER UDF TABLE FUNCTION
//*
//PH02US13 EXEC DSNHC,MEM=DSN8DUWF,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUWF),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUWF),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUWF),
// DISP=SHR
//LKED.IGNORE DD *
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME DSN8DUWF(R)
//*
//* STEP 14: PREPARE CLIENT FOR WEATHER UDF TABLE FUNCTION
//*
//PH02US14 EXEC DSNHC,MEM=DSN8DUWC,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE RENT XREF MARGINS(1,72) OPTFILE(DD:CCOPTS)',
// PARM.LKED='MAP,RENT,REUS,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8DUWC),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8DUWC),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8DUWC),
// DISP=SHR
//LKED.IGNORE DD *
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNELI)
INCLUDE SYSLIB(DSNTIAR)
NAME DSN8DUWC(R)
//*
//* STEP 15: BIND PACKAGE & PLAN FOR WEATHER TBL FUNC CLIENT
//*
//PH02US15 EXEC PGM=IKJEFT01,COND=(4,LT)
//DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//CEEDUMP DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
BIND PACKAGE (DSN8DU!!) MEMBER(DSN8DUWC) -
ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
BIND PLAN (DSN8UW!!) PKLIST(DSN8DU!!.*) -
ACTION(REPLACE) RETAIN +
ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC) SQLRULES(DB2)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
GRANT EXECUTE,BIND ON PLAN DSN8UW!!
TO PUBLIC;
//*
//* STEP 16: INVOKE THE SAMPLE UDF TABLE CLIENT
//*
//PH02US16 EXEC PGM=IKJEFT01,COND=(4,LT),DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSN8DUWC) PLAN(DSN8UW!!) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARMS('DSN!!0.SDSNIVPD(DSN8LWC')
END
//*