EBCIDIC tables with new/chgd column of UNICODE 1208 and native sql procedures using them (sqlcode -2

Brian Laube

EBCIDIC tables with new/chgd column of UNICODE 1208 and native sql procedures using them (sqlcode -2

I have a native sql stored procedure that inserts rows into a table with a "company_name" field. The tablespace is EBCIDIC.
The procedure cannot insert very many special characters with accents because of the limitation of EBCIDIC codepage.

DB2 V11 has the capability to add new columns to a table using a different codepage.
So I went through the process to add a new column "company_name_new" using CCSID 1208.
And then I did a mass update to move existing data from the original "company_name" column to the new "company_name_new".
And then I did some more rename columns and reorgs and I ended with the "company_name" column using CCSID 1208.
So I ended with same table with all the same column names as before and now column "company_name" column is CCSID 1208.

I could then INSERT rows with special characters in the "name" column using simple SQL INSERT via Data Studio.
Excellent. My conclusion is that IBM has made it pretty easy to alter a table to basically add columns with a different codepage. This is very good.

But then I tried to use my existing native sql stored procedure to put rows with special characters in the "company_name" columns.
The special characters did not make it into the table.
I then realized my stored procedure was probably using PARAMETER CCSID EBCIDIC.
So it appears the procedure may be receiving the special character but the procedure working storage could not hold it validly so it did not get into my table in the desired format.

I created a new procedure identical to the original procedure except with different procedure name and procedure attribute of PARAMETER CCSID 1208
And I used the new procedure to successfully insert a row into the table and the "name" column stored the special character appropriately.
So I know it works.

I then went back to the original procedure and attempted to ALTER the procedure PARAMETER attribute but it failed.

ALTER PROCEDURE PVR_UPDATE_COMPANY_CLINIC_PROFILE PARAMETER CCSID UNICODE;
It failed
SQLCODE = -20314, ERROR: THE PARAMETER LIST DOES NOT MATCH THE PARAMETER LIST FOR ALL OTHER VERSIONS OF ROUTINE
DBCYPD4.PVR_UPDATE_COMPANY_CLINIC_PROFILE

The ALTER PROCEDURE syntax diagram suggests there are scenarios to alter the procedure PARAMETER CCSID value... https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sql_alterproceduresqlnative.html
...but apparently (from my experience) going from EBCDIC to UNICODE is not valid.


I find it amusing that IBM made it "easy" to alter (EBCIDIC) tables to add UNICODE columns.
But procedures require a "difficult" drop/recreate of procedures if the procedure is take advantage of this column.

* I have dozens of procedure that must be created/recreated. I would prefer to alter... but I guess I will need to drop/create.

Does anyone have a suggestion for a method to alter the procedure to handle the table column?

Maybe I should make a RFE to IBM....


Regards,
Brian Laube


STATEMENT OF CONFIDENTIALITY The information contained in this email message and any attachments may be confidential and legally privileged and is intended for the use of the addressee(s) only. If you are not an intended recipient, please: (1) notify me immediately by replying to this message; (2) do not use, disseminate, distribute or reproduce any part of the message or any attachment; and (3) destroy all copies of this message and any attachments.