Applying Encryption on a column

1. Apply DB2 encryption on a column:

Step 1:
CREATE TABLE QUALIFIER.TEST_ENCRYPT (ACCT_NBR VARCHAR(32) FOR BIT DATA WITH DEFAULT NULL) CCSID EBCDIC
IN DBNAME.TSNAME AUDIT NONE;
Step 2:
INSERT INTO QUALIFIER.TEST_ENCRYPT
VALUES ENCRYPT_TDES ('12345678','12345678984&&^psw');
Step 3:
UPDATE QUALIFIER.TEST_ENCRYPT
SET ACCT_NBR= ENCRYPT_TDES ('12345678','12345678984&&^psw');
Step 4: (To access the data)
SET ENCRYPTION PASSWORD = 'psw';
select decrypt_char(ACCT_NBR,'12345678984&&^psw') from QUALIFIER.TEST_ENCRYPT ;
--SELECT * FROM QUALIFIER.TEST_ENCRYPT
--WHEN YOU SCREW UP AND DON'T REMEMBER YOUR PASSWORD
DELETE FROM QUALIFIER.TEST_ENCRYPT ;

General Info

1. Reorg tablespace via JCL:
REORG TABLESPACE DBNAME.TSNAME
LOG NO SORTDATA NOSYSREC SORTKEYS DISCARD FROM TABLE QUALIFIER.TABLE_NAME
WHEN (NE_DATE < shelf="DSNSHHA5&DT=" name="RANGE!A1">DASD (z/OS)

2. DASD calculation:
Bytes in a Track = 56,664
Tracks in a Cylinder = 15
Tracks on a Volume = 50,085
Cylinders in a Volume = 3,339
kilobyte = 1024 bytes (1024 = 210), abbreviated KB (Kb is kilobits)
megabyte = 1024 KB = 1,048,576 bytes, abbreviated MB - 1048576
gigabyte = 1024 MB = 1,073,741,824 bytes, abbreviated GB - 1073741824
terabyte = 1024 GB = 1,099,511,627,776 bytes, abbreviated TB - 1099511627776

Using CA tools for multiple tasks

1. Generate Image Copy for a tablespace or an entire DB using CA Tools:
Go to CA DB2 Tools, Database Analyzer, Utility Manager, and then place 'Q' for Quick Path, and then enter the DB/Tablespace name, 'C' and then enter 'A'. Enter 'IC'. Use model library ‘NODE1.ND2.CAI.R11.MODEL' and member 'MJUTLIC2', enter 'Yes' for symbolic FULL, and the desired RETPD.

2. Check restricted tables/indexes:
Go to CA DB2 Tools, Recovery Analyzer (Z), Special Recovery Services (5) and the (1), enter DB name and then 'x's in all.

3. Create Primary key for a table:
Go to CA DB2 Tools, RC/Query, display the table and press enter, alter it, and key in 1, 2, 3 etc under PK for the specific columns.

4. Quick migrate (e.g. to remove statistics etc):
Go to CA DB2 Tools, RC/Migrator, and then Quick Migration and key in 'T' and then the table name, creator and type over 'N' with 'Y' from TS onwards and press PF3, key in 'Y' to update options and enter 'Y' to unload data. Default SQLID can be changed too, for e.g. ABCXYZ.

5. Calculate space for partitioned table space/indexes:
Go to CA DB2 Tools, RC/Query, display the table space and press enter, alter it, and key in 'S' under CMD of part 1, and press enter. On the next screen, change the quantity to the desired number of rows for that partition (e.g. if the table space has 10 parts and total number of estimated rows = 1,000,000, then key in 100,000). Key in 'Totals' at the command line and press enter. The PriQty & SecQty are computed for you.

6. To place a tablespace in 'Utility Pending':
Go to CA DB2 Tools, and go to 'C' Command Processor, and key in:
-STA DB(DBNAME) SPACE(TSNAME) ACCESS (UT)

7. To bring the tablespace back to normal status:
Go to CA DB2 Tools, and go to 'C' Command Processor, and key in:
-STA DB(DBNAME) SPACE(TSNAME) ACCESS (FORCE)