DSNTEJ1U
This JCL creates a database, tablespace, and table with a UNICODE encoding scheme.
//*********************************************************************
//* Name = DSNTEJ1U
//*
//* Descriptive Name = DB2 Sample Application - UNICODE
//* Phase 1 (local site only)
//*
//*
//* Licensed Materials - Property of IBM
//* 5698-DB2
//* COPYRIGHT IBM CORP 1982, 2022
//*
//* STATUS = Version 13
//*
//* Function = This JCL creates a database, tablespace, and table
//* with a UNICODE encoding scheme. The table is then
//* populated from source data in an EBCDIC external
//* format. Finally, the contents of the table are
//* displayed in EBCDIC external and hex formats.
//*
//* Restrictions =
//* Do not run this job unless your system has the
//* prerequisite operating system level for DB2 for OS/390
//* Version 7 Unicode support, as documented in the DB2 for
//* OS/390 Version 7 Program Directory, the section
//* entitled "Function-Dependent Program Requirements".
//*
//* Notice =
//* This sample job uses DB2 Utilities. Some utility functions are
//* elements of separately orderable products. Successful use of
//* a particular sample job may be dependent upon the optional
//* product being licensed and installed in your environment.
//*
//* Pseudocode =
//* PH01US01 Step Drop objects created by prior runs of this job
//* PH01US02 Step Create sample unicode objects
//* PH01US03 Step Load the sample unicode table
//* PH01US04 Step Display the sample UNICODE data in source
//* and hex formats
//*
//* Change Activity =
//* 10/20/2012 Create ALIASes instead of SYNONYMs N0102 / 163503
//* 11/07/2012 Add SET CURRENT SQLID dn1651_inst1 / dn1651
//* 05/17/2013 FIX COPYRIGHT STATEMENT 49779_077_724
//* 02/15/2016 Long data set names PI42601
//* 12/01/2021 Remove tolerance for deprecated TSs e6837 / s15746
//*
//*********************************************************************
//*
//JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR
// DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
// DD DSN=CEE.V!R!M!.SCEERUN,DISP=SHR
//*
//* Step 1: Drop objects from any previous run of this job
//*
//PH01US01 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 ALIAS DEMO_UNICODE;
DROP DATABASE DSN8D!!E;
//*
//* Step 2: Create sammple UNICODE objects
//*
//PH01US02 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 DATABASE DSN8D!!E
STOGROUP DSN8G!!0
BUFFERPOOL BP0
CCSID UNICODE;
CREATE TABLESPACE DSN8S!!U
IN DSN8D!!E
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE
LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID UNICODE
MAXPARTITIONS 254;
COMMIT ;
CREATE TABLE DSN8!!0.DEMO_UNICODE
( LOWER_A_TO_Z CHAR(26),
UPPER_A_TO_Z CHAR(26),
ZERO_TO_NINE CHAR(10),
X00_TO_XFF VARCHAR(256) FOR BIT DATA )
IN DSN8D!!E.DSN8S!!U
CCSID UNICODE;
CREATE ALIAS DEMO_UNICODE
FOR DSN8!!0.DEMO_UNICODE;
GRANT DBADM
ON DATABASE DSN8D!!E
TO PUBLIC;
GRANT USE
OF TABLESPACE DSN8D!!E.DSN8S!!U
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE
ON TABLE DSN8!!0.DEMO_UNICODE
TO PUBLIC;
//*
//* Step 3: Load the sample UNICODE table
//* Note: The LOAD statement below uses a CCSID parm because the
//* source data is in EBCDIC and includes non-text characters
//*
//PH01US03 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSREC00 DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LDU)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSIN DD *
LOAD DATA INDDN(SYSREC00) REPLACE CONTINUEIF(72:80)='X '
CCSID(37, 65534, 65534)
INTO TABLE DSN8!!0.DEMO_UNICODE
( LOWER_A_TO_Z POSITION( 1:26 ) CHAR(26),
UPPER_A_TO_Z POSITION( 28:53 ) CHAR(26),
ZERO_TO_NINE POSITION( 55:64 ) CHAR(10),
X00_TO_XFF POSITION( 66 ) VARCHAR )
SORTDEVT SYSDA SORTNUM 4
//*
//* Step 4: Display the sample UNICODE data in source and hex format
//*
//PH01US04 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//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';
SELECT LOWER_A_TO_Z,
HEX( LOWER_A_TO_Z ) AS LOWER_A_TO_Z_HEX ,
LENGTH( LOWER_A_TO_Z ) AS LOWER_A_TO_Z_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT UPPER_A_TO_Z,
HEX( UPPER_A_TO_Z ) AS UPPER_A_TO_Z_HEX ,
LENGTH( UPPER_A_TO_Z ) AS UPPER_A_TO_Z_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT ZERO_TO_NINE,
HEX( ZERO_TO_NINE ) AS ZERO_TO_NINE_HEX ,
LENGTH( ZERO_TO_NINE ) AS ZERO_TO_NINE_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 1, 32) AS X00_TO_X20,
HEX( SUBSTR(X00_TO_XFF, 1, 32)) AS X00_TO_X20_HEX,
LENGTH( SUBSTR(X00_TO_XFF, 1, 32)) AS X00_TO_X20_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 33, 32) AS X21_TO_X40,
HEX( SUBSTR(X00_TO_XFF, 33, 32)) AS X21_TO_X40_HEX,
LENGTH( SUBSTR(X00_TO_XFF, 33, 32)) AS X21_TO_X40_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 65, 32) AS X41_TO_X60,
HEX( SUBSTR(X00_TO_XFF, 65, 32)) AS X41_TO_X60_HEX,
LENGTH( SUBSTR(X00_TO_XFF, 65, 32)) AS X41_TO_X60_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 97, 32) AS X61_TO_X80,
HEX( SUBSTR(X00_TO_XFF, 97, 32)) AS X61_TO_X80_HEX,
LENGTH( SUBSTR(X00_TO_XFF, 97, 32)) AS X61_TO_X80_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 129, 32) AS X81_TO_XA0,
HEX( SUBSTR(X00_TO_XFF, 129, 32)) AS X81_TO_XA0_HEX,
LENGTH(SUBSTR(X00_TO_XFF, 129, 32)) AS X81_TO_XA0_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 161, 32) AS XA1_TO_XC0,
HEX( SUBSTR(X00_TO_XFF, 161, 32)) AS XA1_TO_XC0_HEX,
LENGTH(SUBSTR(X00_TO_XFF, 161, 32)) AS XA1_TO_XC0_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 193, 32) AS XC1_TO_XE0,
HEX( SUBSTR(X00_TO_XFF, 193, 32)) AS XC1_TO_XE0_HEX,
LENGTH(SUBSTR(X00_TO_XFF, 193, 32)) AS XC1_TO_XE0_LEN
FROM DSN8!!0.DEMO_UNICODE;
SELECT SUBSTR(X00_TO_XFF, 225, 32) AS XE1_TO_XFF,
HEX( SUBSTR(X00_TO_XFF, 225, 32)) AS XE1_TO_XFF_HEX,
LENGTH(SUBSTR(X00_TO_XFF, 225, 32)) AS XE1_TO_XFF_LEN
FROM DSN8!!0.DEMO_UNICODE;
//* 