Monday, July 29, 2013

Difference between syncsort and Dfsort

As a beginner, we are often stuck with the doubt ' Are SYNCSORT and DFSORT same?'.

DFSORT IS IBM’S PRODUCT AND SYNCSORT IS PRODUCT OF SYNCSORT COMPANY.
The basic functions of both are almost same like SORT,MERGE,COPY and other benefits.

ICETOOL is the utility for DFOSRT.
SYNCTOOL is in the same way a tool for SYNCSORT.
For getting syncsort manual you need to give the licensed CPU serial number. DFOSRT manual is freely available.
The easiest way to know if your z/os supports syncsort or dfsort is to look at the messages in sysout for the SORT step.
DFSORT message begins with ICE*
SYSNCORT message begins with WER*.

Wednesday, July 24, 2013

Some more SYNCSORT/DFSORT Examples with JCL

Earlier we have seen the basic Sort example to start of with. Here lets cover some more typical   SORT examples

ALTSEQ in SYNCSORT/DFSORT
//**************************************************
//STEP02   EXEC PGM=SYNCSORT
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN   DD DSN=TEST.SORT.INPUT,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUTPUT,
//            DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=400,BLKSIZE=8800),
//            SPACE=(TRK,(350,200),RLSE)
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
      SORT FIELDS=COPY
       ALTSEQ CODE=(E340,C940,4D40,5D40)
       OUTREC FIELDS=(1,80,TRAN=ALTSEQ)
/*
EXPLANATION:

ALTSEQ will replace the character in the INPUT File with that specified.Here in ALTSEQ code we are specifying 
ALTSEQ CODE=(E340,C940,F540,5D40).
'E3' is the hexadecimal value for alphabet 'I'.
'40' is the hex equivalent for SPACE. 
So ALTSEQ CODE=E340 will replace 'I' with 'SPACE'. 
Like wise we can do for any characters provided we know the HEX equivalent of that character.
Similarly '4D40' will replace '5' with 'SPACE'. So in OUTPUT we will see 'I' and '5' getting replaced by SPACE.

INPUT:
**************
INDIA    MIKA
INDIA    1500
SWEDEN   2500
SPAIN    1096
TURKEY   2000
BRAZIL   6700
HOLLAND  3456
NEPAL    1209
OUTPUT:













SYNCSORT TO GET COUNT OF RECORDS
//STEP02   EXEC PGM=SYNCSORT
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN   DD DSN=TEST.SORT.INPUT,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,
//            DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//            SPACE=(TRK,(350,200),RLSE)
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
      SORT FIELDS=(1,3,CH,A)
      OUTFIL REMOVECC,NODETAIL,
      TRAILER1=('NO OF RECORDS:',COUNT=(M11,LENGTH=8))
/*
REMOVECC omits the ANSI carriage control character from all of the report records.
NODETAIL generates a report with no data records.
SYNCSORT TO PRINT A LINE AFTER EVERY  RECORDS
//STEP02   EXEC PGM=SYNCSORT
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN   DD DSN=TEST.SORT.INPUT2,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,
//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//            SPACE=(TRK,(350,200),RLSE)
//*ORTOF02 DD DUMMY
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
      SORT FIELDS=COPY
      OUTFIL BUILD=(1,80,/,80C'-')

/*
SYNCSORT TO EXTRACT A RECORD USING SUB STRING CONDITION.
//STEP02   EXEC PGM=SYNCSORT
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN   DD DSN=TEST.SORT.INPUT2,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,
//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//            SPACE=(TRK,(350,200),RLSE)
//*ORTOF02 DD DUMMY
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
      INCLUDE COND=(1,20,SS,EQ,C'IS')
      SORT FIELDS=COPY
/*
SS looks for the sub string 'IS' in the position 1 to 20 in the input file and puts that reocrd in the output. 


SYNCSORT TO CONVERT PACKED DECIMAL TO ZONNED DECIMAL
//STEP02   EXEC PGM=SYNCSORT
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN   DD DSN=TEST.SORT.INPUT2,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,
//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//            SPACE=(TRK,(350,200),RLSE)
//*ORTOF02 DD DUMMY 
//SYSOUT   DD SYSOUT=*
//SYSIN    DD * 
      SORT FIELDS=COPY
      OUTREC FIELDS=(1,5,PD,ZD) 
/*
input:
----+
*****
1223A
23434
*****
Output:
----+----1
**********
.1.2.2.3.
.2.3.4.3
**********
JOINKEYS  for SYNCSORT
Use sort to filter out matched and unmatched record
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTJNF1 DD DSN=TEST.SORT.INPUT2,DISP=SHR
//SORTJNF2 DD DSN=TEST.SORT.INPUT3,DISP=SHR
//SORTOF01 DD DSN=TEST.SORT.OUTPUNT,
//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//            DCB=(*.SORTJNF1),
//            SPACE=(TRK,(350,200),RLSE)
//SORTOUT  DD DUMMY
//SYSOUT   DD SYSOUT=*
//SYSIN    DD *
      JOINKEYS FILES=F1,FIELDS=(1,5,A)
      JOINKEYS FILES=F2,FIELDS=(1,5,A)
      JOIN UNPAIRED,F1,ONLY
      REFORMAT FIELDS=(F1:1,5)
      SORT FIELDS=COPY
      OUTFIL FILES=01,BUILD=(1,5) 
/
EXPLANATION:The above JCL will filter out the unmatched record from INPUT2 by comparing with INPUT3. 
To find out the matching record we need to use.
//SYSIN    DD *
      JOINKEYS FILES=F1,FIELDS=(1,5,A)
      JOINKEYS FILES=F2,FIELDS=(1,5,A)
      REFORMAT FIELDS=(F1:1,5)
      SORT FIELDS=COPY
      OUTFIL FILES=01,BUILD=(1,5)
/*
DFSORT  TO WRITE HEADER ,TRAILER RECORDS
SYSIN DD*
OPTIONS COPY

OUTFIL REMOVECC,
TRAILER1=('TOTAL:',TOT=(10,6,ZD))
Adding the length and mask sub parameter:
SORT FIELDS=COPY                             
OUTFIL REMOVECC,NODETAIL,                    
TRAILER1=('TOTAL:',TOT=(10,6,ZD,M1,LENGTH=9))
 


To Write more than one Trailer we need to use the keywords "Trailer1,Trailer2,Trailer3."
we will see how to write trailer for count of records and total of records.

OPTION COPY                                                   
OUTFIL REMOVECC,                                               TRAILER1=('TOTAL:',TOT=(10,6,ZD,LENGTH=10,EDIT=(TTTTTTTTTT))),
TRAILER2=('COUNT:',COUNT=(LENGTH=10))
 
                     
 


Explanation of the keywords used: TRAILER1,TRAILER2,TRAILER3,COUNT,TOT are the keywords for SORT cards.OUTFIL is used to print the reports.REMOVECC in sort is used to remove the Cariage control inserted by DFSORT in first position.
The value of '1' in the first position of a record tells the printer to start a new page.To remove these carriage control, we need to use REMOVECC in OUTFIL statement.

TOT=(10,6,ZD) will make the total on 6 digits starting in 10th column.
If we use NODETAIL,then we would see only the trailer and header records.Other records would not be shown in output.
Omitting NODETAIL in OUTFIL would ensure we see all records along with trailer and header.


Output:
----+----1----+----2----+----3-- 
******************************** 
HARLEY   123456MEXICO            
DAVID    658999CANADA            
COUNT:        2                  
TOTAL:0000782455                 
******************************** 
    


To Add Header in SORT using HEADER1 parameter :
OPTION COPY                                                   
OUTFIL REMOVECC,                                              
HEADER1=('REPORT GENERTED AS ON:',&DATE,//,22C'-'),           
TRAILER1=('TOTAL:',TOT=(10,6,ZD,LENGTH=10,EDIT=(TTTTTTTTTT))),
TRAILER2=('COUNT:',COUNT=(LENGTH=10))
                         


Output:
*********************************
REPORT GENERTED AS ON:03/02/13   
---------------------            
HARLEY   123456MEXICO            
DAVID    658999CANADA            
COUNT:        2                  
TOTAL:0000782455                 
******************************** 


DFSORT  TO COMPARE THE HEXCODE/ASCII OF CHARACTER ALPHABETS.
//STEP02   EXEC PGM=SORT 
//SORTIN   DD DSN=TEST.SORTINC,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUT1,
//            DISP=(NEW,CATLG,DELETE),UNIT=(SYSDA,59),
//            DCB=*.SORTIN, 
//            SPACE=(TRK,(50,100),RLSE) 
//SYSOUT   DD SYSOUT=* 
//SYSPRINT DD SYSOUT=* 
//SYSIN    DD *
SORT FIELDS=COPY  
INCLUDE COND=(3,1,AC,GE,X'41',AND,3,1,AC,LE,X'4F')

Input:
00B0000
00A1462
00C1850
00D2108
00E2109
00FM006
00ZM007
00ZM008
00YM023
00CM050


Output:
00B0000
00A1462
00C1850
00D2108
00E2109
00FM006
00CM050

Explanation: The above sort card checks for the characters from A to O.  All other characters will be eliminated. '41' hex of 'A' and '4F' is hex of 'O' in ASCII.
DFSORT  TO INSERT/ADD  DELIMITER/CHARACTERS AFTER EVERY RECORD.

//SYSIN    DD *                             
SORT FIELDS=COPY                            
INREC BUILD=(1,60,SQZ=(SHIFT=LEFT,MID=C'~'))

Explanation:MID=C'`' tells DFSORT to insert the character between the fields.

SORT  TO REMOVE SPACES BETWEEN CHARACTERS
SQZ operator in DFSORT/SYNCSORT can be used to remove spaces between characters.
Input:Q WE R T Y 
Expected Output: QWERTY
We can use SQZ operator to remove the spaces and format the field.
OPTION COPY                           
OUTREC FIELDS=(1,40,SQZ=(SHIFT=LEFT)) 

Explanation: We are squeezing out the blanks and  shifting the characters to the left for all the data in thje positions 1 to 40.

Monday, July 22, 2013

DB2 SQL Frequently used Queries



How to fetch last n rows only in DB2

You can use ORDER BY clause ... the syntax is as below

SELECT COL_A

FROM YOUR.TABLE

WHERE KEY_A = :KEY-A

ORDER BY COL_A  DESC

FETCH FIRST n ROWS ONLY

How to look for LOW VALUES in DB2

SELECT CLIENT_NAME FROM QUALIER.TABLE_NAME
where CLIENT_NAME  like X'6C006C' 
 The 6C is the hex equivalent of the '%' sign, This will return any rows with low values anywhere in the column

How to find relationships between tables
Suppose we have a table with a column name 'SITE_CD'. We want to find out what all table have the same cloumn name in the system.

SELECT SUBSTR(TBNAME,1,18) AS TABLE FROM SYSIBM.SYSCOLUMNS
WHERE NAME = 'SITE_CD'  
AND TBCREATOR = 'schema-name'
ORDER BY  TABLE
***AND TBCREATOR = 'schema-name'  :   is optional
like RBM00.CLINT WHERE RBM00 IS OPTIONAL

Find the PRIMARY KEY of a Table

SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'BZK00'
AND TBNAME = 'CLIENY_NAME'
AND KEYSEQ > 0
ORDER BY KEYSEQ

DATE/TIMSTAMP

SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1
SELECT CURRENT DATE  FROM SYSIBM.SYSDUMMY1
SELECT CURRENT TIME  FROM SYSIBM.SYSDUMMY1
SELECT DAYS('2010-10-31') - DAYS('1990-10-10')  FROM SYSIBM.SYSDUMMY1

Change the values of column using TRANSLATE in DB2

Translate option can be used to change a character to another in a column.

UPDATE  Schema.TBNAME
set COLUMN_NAME = TRANSLATE (COLUMN_NAME,'A','C')
where TRN_ID ='2000-09-21-01.09.14.828223'

It will scan all the characters in the column 'COLUMN_NAME' which are 'C' and change them to 'A'.
'A' , 'C' can be numeric/non-numeric combination also.


Find out DB2 locks holding up table space or tables
 
Go to DB2 command window and give the below command
-dis db(XXXXXX0 sp(XXXXXX) USE
you also can change the parameter USE to LOCKS or also CLAIMERS
SP means the Tablespace name or INDEXNAME

DB2 CATALOG TABLES & DESCRIPTION

The following represents DB2 catalogue tables and their descriptions.

SYSIBM.IPNAMES  :Defines the DRDA servers DB2 can access using TCP/IP
SYSIBM.LOCATIONS: Contains row for every accessible remote server.
SYSIBM.LULIST: Contains multiple logical unit names to be specified for a given location.
SYSIBM.LUMODES: This table is accessed only during initial conversation-limit negotiation between DB2 and remote LU.
SYSIBM.LUNAMES: Contains rows for each SNA client or server that communicates wit DB2.
SYSIBM.MODESELECT: Associates a MODE name with any conversation created to support and outgoing SQL request.
SYSIBM.SYSAUXRELS: Contains 1 row for each auxiliary table created for a LOB column
SYSIBM.SYSCHECKDEP: Contains 1 row for each reference to a column in a table check constraint.
SYSIBM.SYSCHECKS: Contains 1 row for each table-check constraint.
SYSIBM.SYSCOLAUTH: Records the UPDATE or PRIVILEGE preference that are held by users in individual columns of a table or view
SYSIBM.SYSCOLDIST: Contains 1 or more rows or the first key column of an index key.
SYSIBM.SYSCOLUMNS: Contain 1 row for every column of each table or view
SYSIBM.SYSCOPY: Contains the information needed for Recovery
SYSIBM.SYSDATABASE: Contains 1 row for each database
SYSIBM.SYSDBAUTH : Contains privileges held by users over databases.
SYSIBM.SYSDBRM: Contains one row for DBRM of each application plan



Tuesday, July 9, 2013

Sample JCL for UNLOAD & LOAD utility for DB2 TABLE - What is CHECK & COPY PENDING

Below are the Sample JCLs for unload and load utility in DB2.
Loading and Unloading of data from DB2 TABLE

The below JCL shows how to UNLOAD data from one table to a dataset using IKJEFT01.

//STEP02   EXEC PGM=IKJEFT01,DYNAMNBR=50
//SYSTSPRT DD     SYSOUT=$
//SYSPRINT DD     SYSOUT=$
//SYSUDUMP DD  SYSOUT=$
//SYSTSIN  DD DSN=TEST.PROCLIB(UNLD),DISP=SHR
//SYSPUNCH DD DSN=TEST.TABLE.STRTCTURE,

//                                       DISP=(NEW,CATLG,DELETE),
//                                       UNIT=DISK,SPACE=(CYL,(1,1),RLSE)
//SYSREC00 DD DSN=TEST.TABLE.LOAD,
//                                     DISP=(,CATLG,DELETE),
//                                     UNIT=CART,

//                                     DCB=(SYS3.DSCB),LABEL=EXPDT=99000
//SYSIN    DD *
SELECT *  FROM TST.CLIENT ;
/*

//*

SYSIN DD* contains the condition for the SQL query we want to use for fetching the data.
SYSTSIN  DD DSN=TEST.PROCLIB(UNLD) ->  This will contain the details like DB2 level(test or prod) ie,from where we are dowloading. Also the utility, plan , and the library used to pickup the utility like below.SYSREC00 will contain the actual unloaded records from DB2.









The above JCL  one loads the table CLIENT into TAPE. For loading to DISK use below.

//SYSREC00 DD DSN=TEST.TABLE.LOAD,
//                                     DISP=(,CATLG,DELETE),
//                                     UNIT=DISK,SPACE=(CYL,(100,100),RLSE)


Load the downlaoded data from dataset to test DB2 Table
 

This JCL shows how we can upload the data from Flat File back to DB2 Table.

//LOADTB    EXEC DB2LOAD,LDNM=TBLOAD1,DB2=DB2T
//LOAD.SYSREC00 DD  DSN=TEST.TABLE.LOAD,DISP=SHR
//*
//LOAD.SYSIN    DD  DSN=TEST.TABLE.STRTCTURE,DISP=SHR
//*


DB2LOAD is the utility used to execute the load function.
LBNM=TBLOAD1 ::  We can use any name here inplace of TBLOAD1
DB2=DB2T:: indicates a test database region.
TEST.TABLE.LOAD  contains the data to be loaded into the table which was downloaded in the unload step.
LOAD.SYSIN contains the table structure along with additional parameters to load the table.

like
LOAD DATA REPLACE LOG NO ENFORCE NO INDDN SYSREC00
INTO TABLE TST.CLIENT.










NOTE: We Get the table structure in the dataset with DD name SYSPUNCH while we unload the data in previous step

CHECK PENDING AND COPY PENDING:

Check pending is a tablespace/index status set when DB2 has reason to believe that an object might be in an inconsistent state. Usually happens as  a result of running LOAD on a tablespace containing a table that has Referential Integrity constraints, but  that was  not enforced during the LOAD OPERATION (ENFORCE NO). (like we used in above example). Recovering and RI tablespace to a previous point in time can also set it. None of this is directly related to the design of the application.If CHECK PENDING is set you can run the CHECK utility to instruct DB2 to verify that the data is consistent and switch off the pending status.
 To remove the Checkpending condition we need to  execute the following commands on tablespace
for all the affected table space.

REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLA00DB.TBLB01TS
NOCHECKPEND
REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLB00DB.TBLB02TS
NOCHECKPEND


COPYPENDING
Any table space will be in copy pending status after  load is performed on the DB2 table,
with log no parameter
or
if load is performed without taking an image copy
or
if the job abend during load
or
if NOCOPYPEND parameter is not specified in syspunch dataset 
To remove the Copypending  condition we need to  execute the following commands on tablespace for all the affected table space. 


REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLA00DB.TBLB01TS
NOCOPYPEND
REPAIR  OBJECT  LOG NO
SET  TABLESPACE TBLB00DB.TBLB02TS
NOCOPYPEND 


Is There any way to check which operation has put the table on pending status?? 

Yes,We can know this from SYSIBM.SYSCOPY Table.
If we run a query against this table, the ULITY values can help us in determining the desired operation.
example:  I want to check what last operation was performed by my id or any id.
So, i Can query the SYSIBM.SYSCOPY Table like below

SELECT DSNAME SPACE, ICTYPE AS UTILITY, TIMESTAMP
FROM SYSIBM.SYSCOPY WHERE AUTHID='my-id'
ORDER BY TIMESTAMP
If we check the output, check for UTILITY  values

UTILITY Can have these values like 'A', 'S' .. like below.  
A-Alter
B-REBUILD IX
D-CHECKDATA LOG(NO)
F-full IC
I-Incremental IC
P-partial recovery point
Q-Quiesce
R-LOAD REPLACE LOG(YES)
S-LOAD REPLACE LOG(NO)
T-terminated utility
W-REORG LOG(NO)
X-REORG LOG(YES)
Y-LOAD RESUME LOG(NO)
Z-LOAD RESUME LOG(YES)
Thus we can check the operation last performed. 

Common errors encountered while loading a table

UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'0B37'

Check the spool for which dataset it is abending with SB37.
Then we need to change the UNIT parameter.
If it is using SYSDA, we can use multivolume coding the SYSDA like
UNIT=(SYSDA,59),
SPACE=(CYL,(200,200),RLSE)

DSNURSIX - KEY COUNT INCONSISTENT FOR SORT-RD PHASE,NUMBER OF KEYS EXPECTED =72379826, NUMBER OF KEYS PROCESSED=8681906

DSNUGSOR - SORT PHASE STATISTICS - 
NUMBER OF RECORDS=0
ELAPSED TIME=00:00:00
 DSNURSIX - KEY COUNT INCONSISTENT FOR SORT-RD PHASE, NUMBER OF KEYS
PROCESSED=0
DSNUGBAC - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E4030A


Resolution:
1. Increase the SORT datafiles used internally in the load utility.
2. Terminate the UTLITY
How to terminate?
use the command -TERM UTIL(UTIL-NAME)
What is this UTIL-NAME?
Its just the LDNM name we use in our load job. In the above example, the utility name is

TBLOAD1 
where to use this command?
Go to  DB2I PRIMARY OPTION MENU -> option 7(DB2 COMMANDS)
3. Restart the load job


ERROR LOADING INDEX, ERROR = X'00E40322' INDEX = Bth00.D4xx30X1
DSNUGBAC -RESOURCE UNAVAILABLE
      REASON 00D70014
      TYPE 00000220
      NAME TEX2.DSNDBC.DXXXXDB.D890000X1.I0001.A001
UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40322'


Explnation: The load phase of the load job worked, but the rebuilding index failed.
00D70014 - An attempt to extend a data set failed, because a problem was detected in media manager services.

Resolution:
1.Check the allocations for the index of the table.It must be too small.
We need to increase it, Both primary and seconday quantity for all the index available for the table.
2. Terminate the utility using the same command  -TERM UTIL(UTIL-NAME)
3. restart the load job again. 

Friday, July 5, 2013

UNABLE TO DETERMINE ADDRESS while calling a CICS program

Mostly we get this type of error when we call a NON-CICS pogram from a CICS program. This occurs more obviously if we use a CICS COBOL complier and compile the NON-CICS COBOL program. Generally we use DFHCOMMAREA along with other LINKAGE SECTION  variables while calling sub-pgm. But internally the CICS compiler inserts the variable DFHEIBLK along with the LINKAGE SECTION parameters

DFHEIBLK:  (Data facility Heirarchy Exec Interface BlocK) is a cobol copy book which will be automatically included by the cics translator in all the cics programs.This will contain PIC clauses of all the fields that are mandatory in any cics program. 

Thus there is a address mismatch and it says 'UNABLE TO DETERMINE ADDRESS'. There is a mismatch between what the linkage section is sending and what the linkage section in the called program is receiving.
 To Overcome this situation, we need to include DFHEIBLK along with other parameters passed in the linkage section  in both  the called and calling program respectively.

DB2 TDES Encryption on Mainframe

TDES Encryption Algorithm can be used in DB2 tables as well. Suppose confidential numbers needs to be stored .We can ENCRYPT the value and store it.
Lets create the DB2 table like below


CREATE TABLE U202.CUST_DATA                                      
     (U_STR                CHAR(3) FOR SBCS DATA NOT NULL,         
      U_REG                CHAR(6) FOR SBCS DATA NOT NULL,         
      U_SKU                INTEGER NOT NULL,                       
      U_GIFT               CHAR(3) FOR SBCS DATA NOT NULL,         
      U_CREDIT_CARD        VARCHAR(40) FOR BIT DATA NOT NULL)

Now lets insert values into the table:

SET ENCRYPTION PASSWORD ='PWD1APRIL@' 
INSERT INTO U202.CUST_DATA                         
 VALUES('001', '123456', 1234, 'LCD', ENCRYPT_TDES ('9876543210123456'))

Now To Decrypt 

SET ENCRYPTION PASSWORD ='PWD1APRIL@';                       
SELECT DECRYPT_CHAR ("U_CREDIT_CARD") FROM U202.CUST_DATA