CCSID Mismatch after applying PTF for DB2 V7 to V8 migration

To begin with the DB2 migration from V7 to v8, we need to apply PTFs. After applying V7 PTF, we got the following error which accessing SPUFI.
DSNESP06
DSNE345I WARNING: DB2 DATA CORRUPTION CAN RESULT
FROM THIS SPUFI SESSION BECAUSE THE
CCSID USED BY THE TERMINAL IS NOT THE
SAME AS THE CCSID USED BY SPUFI
- TERMINAL CCSID:
- SPUFI CCSID: 500
NOTIFY THE DB2 SYSTEM ADMINISTRATOR.
PRESS: ENTER to continue END to return

To get rid of the problem, we have changed one default as below:After getting the first screen, press enter. You will see:Enter the following to control your SPUFI session:
1 SQL TERMINATOR .. ===> ; (SQL Statement Terminator)
2 ISOLATION LEVEL ===> CS (RR=Repeatable Read, CS=Cursor Stability)
3 MAX SELECT LINES ===> 30000 (Maximum number of lines to be returned from a SELECT)
4 ALLOW SQL WARNINGS===> NO (Continue fetching after sqlwarning)
5 CHANGE PLAN NAMES ===> yes (Change the plan names used by SPUFI) Output data set characteristics:
6 RECORD LENGTH ... ===> 4092 (LRECL=Logical record length)
7 BLOCK SIZE ...... ===> 4096 (Size of one block)
8 RECORD FORMAT ... ===> VB (RECFM=F, FB, FBA, V, VB, or VBA)
9 DEVICE TYPE ..... ===> SYSDA (Must be DASD unit name) Output format characteristics:
10 MAX NUMERIC FIELD ===> 33 (Maximum width for numeric fields)
11 MAX CHAR FIELD .. ===> 80 (Maximum width for character fields)
12 COLUMN HEADING .. ===> NAMES (NAMES, LABELS, ANY or BOTH)
PRESS: ENTER to process END to exit
HELP for more information'
Change the default from “NO” to “YES” for point 5 of plan names Then you will arrive at:
CURRENT SPUFI DEFAULTS - PANEL 2 SSID: DSNX ===> Enter the following to control your SPUFI session:
1 CS ISOLATION PLAN ===> DSNESPCS (Name of plan for CS isolation level)
2 RR ISOLATION PLAN ===> DSNESPRR (Name of plan for RR isolation level) Indicate warning message status:
3 BLANK CCSID WARNING ===> no (Show warning if terminal CCSID is blank)

As from the first screen, it is confirmed that terminal CCSID is coming as blank. This is the most simple solution to avoid this. Root cause: Prior to V7, Spufi CCSID comparision was not incorporated, but when you install the PTF, the SPUFI is enabled to compare it. That’s why you will get this. But all the problem is not clear until we apply the same PTFs on our prod LPAR.

Now I am going to explain, if the terminal CCSID is not Blank, how to solve the problem.The error screen is as below:
DSNESP06
DSNE345I WARNING: DB2 DATA CORRUPTION CAN RESULT
FROM THIS SPUFI SESSION BECAUSE THE
CCSID USED BY THE TERMINAL IS NOT THE
SAME AS THE CCSID USED BY SPUFI
- TERMINAL CCSID: 37
- SPUFI CCSID: 500
NOTIFY THE DB2 SYSTEM ADMINISTRATOR.
PRESS: ENTER to continue END to return

As per IBM recommendation we need to bind 2 additional packages and 2 plans to get rid of this:
BIND PACKAGE (SPCS0037)
ACTION (REPLACE)
MEMBER (DSNESM68) LIBRARY ('D710.SDSNDBRM')
OWNER (TSGDSN3)
QUALIFIER (TSGDSN3)
SQLERROR (NOPACKAGE)
VALIDATE (RUN)
ISOLATION (CS)
CURRENTDATA (YES)
KEEPDYNAMIC (NO)
NOREOPT (VARS)
DEGREE (1)
DBPROTOCOL (DRDA)
ENCODING (37)
EXPLAIN (NO);

BIND PACKAGE (SPRR0037)
ACTION (REPLACE)
MEMBER (DSNESM68) LIBRARY ('D710.SDSNDBRM')
OWNER (TSGDSN3)
QUALIFIER (TSGDSN3)
SQLERROR (NOPACKAGE)
VALIDATE (RUN)
ISOLATION (RR)
CURRENTDATA (YES)
KEEPDYNAMIC (NO)
NOREOPT (VARS)
DEGREE (1)
DBPROTOCOL (DRDA)
ENCODING (37)
EXPLAIN (NO);

BIND PLAN (SPCS0037)
OWNER (TSGDSN3)
QUALIFIER(TSGDSN3)
ACTION (REPLACE)
RETAIN
PKLIST (SPCS0037.DSNESM68 )
VALIDATE (RUN)
ISOLATION (CS)
ACQUIRE (USE)
RELEASE (COMMIT)
NODEFER (PREPARE)
DBPROTOCOL (PRIVATE)
CACHESIZE (256)
CURRENTDATA (YES)
DISCONNECT (EXPLICIT)
SQLRULES (DB2)
DEGREE (1)
NOREOPT (VARS)
KEEPDYNAMIC (NO)
ENCODING (37)
EXPLAIN (NO);

BIND PLAN (SPRR0037)
OWNER (TSGDSN3)
QUALIFIER(TSGDSN3)
ACTION (REPLACE)
RETAIN
PKLIST (SPRR0037.DSNESM68 )
VALIDATE (RUN)
ISOLATION (RR)
ACQUIRE (USE)
RELEASE (COMMIT)
NODEFER (PREPARE)
DBPROTOCOL (PRIVATE)
CACHESIZE (256)
CURRENTDATA (YES)
DISCONNECT (EXPLICIT)
SQLRULES (DB2)
DEGREE (1)
NOREOPT (VARS)
KEEPDYNAMIC (NO)
ENCODING (37)
EXPLAIN (NO);
After Bind and getting the first screen and pressing enter there, you will get the screen as before.You go to modify as before: change CHANGE PLAN NAMES from NO to YES In the following screen, you need to change the default DB2 supplied Plan name as below:
CURRENT SPUFI DEFAULTS - PANEL 2 SSID: DSNX
===> Enter the following to control your SPUFI session:
1 CS ISOLATION PLAN ===> SPCS0037 (Name of plan for CS isolation level)
2 RR ISOLATION PLAN ===> SPRR0037 (Name of plan for RR isolation level) Indicate warning message status:
3 BLANK CCSID WARNING ===> no (Show warning if terminal CCSID is blank)

Here are other info that may be necessary while in New Function mode:
*************************************************************
*** Further instructions for DB2 Version 8 customers *** *************************************************************
In V8 new-function mode only, plans for SPUFI require an additional package for the DSNTIAP DBRM. Thus, if your DB2 is now running in V8 new-function mode, you also need to use a command like the following to bind
DSNTIAP DBRM under CCSID 1047:
BIND PACKAGE(TIAP1047) MEMBER(DSNTIAP) -
ACTION(REPLACE) ISOLATION(CS) ENCODING(1047) -
LIBRARY('prefix.SDSNDBRM') and then include the TIAP1047 collection id when you BIND the plans for SPUFI:
BIND PLAN(SPCS1047) -
PKLIST(SPCS1047.DSNESM68, -
TIAP1047.DSNTIAP) -
ISOLATION(CS) ENCODING(1047) ACTION(REPLACE) ...
BIND PLAN(SPRR1047) -
PKLIST(SPRR1047.DSNESM68, -
TIAP1047.DSNTIAP) -
ISOLATION(RR) ENCODING(1047) ACTION(REPLACE)
If your DB2 is currently running in V8 compatibility mode or in enabling-new-function mode, you need to bind DSNTIAP as indicated above after DB2 enters new-function mode.

2. After you have created the new packages and plans, grant access on them to the target user group.

3. Instruct the users to display the CURRENT SPUFI DEFAULTS panel (DSNESP02) and specify YES in the CHANGE PLAN NAMES field. When they do this, SPUFI displays CURRENT SPUFI DEFAULTS - PANEL 2 (DSNESP07). Instruct users to specify SPCS1047 for CS ISOLATION PLAN and SPRR1047 for RR ISOLATION PLAN. These users can now update SPUFI without receiving the DSNE345Imessage.

Cheers....
Prakash Singh
IBM Certified Database Administrator (DB2 on Mainframe)