PHP and CCSID Conversion

[FaStOnE]

I have several tables that I'm pulling data from on the i5... I just found out that I'm getting garbage out of ONE table! After throwing the Red Flag and forcing a Review of the Call on the Field... the Booth came back and said there were issues with the CCSID. The ONE table is set to 65535. The rest are set to 37.

QCCSID is set for 37.

UserID is set for 37.

Is there a way to convert this on-the-fly as I'm pulling the data via DB2_Execute? Or, do I have to convert the table on the i5?

If I'm converting ... what's the best case scenario for doing just that?

[FaStOnE]

Re: PHP and CCSID Conversion

OK, new findings ...

I found out the source has compiled the PF as 66535. So, how am I going to get this to pull the ASCII data from the PF into the Select Statement in PHP?

[jamief]

Re: PHP and CCSID Conversion

how are you connecting to the data? --- is the php script running on a stand alone LINUX box?
I only ask cause I just setup a stand alone linux server and am about to try the same thing.

I can pull any data from dds created tables on the i from remote microsoft server thru asp on windows 2007 server.
Not sure why PHP would be any different.

any knowledge on this subject will be greatly appreciated.

Jamie

[FaStOnE]

Re: PHP and CCSID Conversion

No, I'm running this from my Laptop running Win2000.

I'm pulling data from other tables (non 65535) without issue. That's my issue. When I change the Select statement to go to one of those tables, it gives me garbage. So, can't help you with the Linux setup... sorry!

[littlepd]

Re: PHP and CCSID Conversion

Forgive me for I know not what PHP does for database access. So, back to Jamie's question: How are you connecting to the data? Is there an ODBC or JDBC connection that you set up? If so, which one? Does it have a Force Translate parameter?

[FaStOnE]

Re: PHP and CCSID Conversion

Using DB2_Connect with DB2_Prepare and DB2_Execute. None of those (that I know of) give you a Force Translate parameter. That's kinda what I'm looking for .. the capability to do that ... if needed

[littlepd]

Re: PHP and CCSID Conversion

Check the attachment from a powerpoint presentation from an IBMer

[littlepd]

Re: PHP and CCSID Conversion

IBM DB2 for i

Tips and Techniques for DB2 Connect


This document contains tips on configuring the IBM DB2 Connect product to access the DB2 on iSeries & AS/400. (Note: IBM's DB2 Connect does not provide any significant performance improvements over the IBM Client Access Express ODBC driver that's included with every iSeries & AS/400 server).

If you choose to use DB2 Connect for data access, then it is highly recommended that connection is configured with the DB2 Connect Client Configuration Assistant. Click here (PDF, 48.7KB) for detailed instructions on configuring an iSeries & AS/400 connection with DB2 Connect. NOTE: If a TCP/IP connection is desired, then your AS/400 server cannot have a release prior to V4R2 installed.

If you really want to manually configure the connection via the DB2 command line, then you will need to enter catalog commands similar to the following:

1. > db2 catalog tcpip node as400 remote as400.somecompany.com server 446
2. > db2 catalog db as400db at node as400 authentication dcs
3. > db2 catalog dcs db as400db as mydbsvr

Where "as400db" is your local alias for the database and "mydbsvr" is the local RDB name of the AS/400 (set via WRKRDBDIRE).

If you catalogued the DB2 for i server incorrectly, you may get an SQL5048N error message. SQL7008N is another common error is that the DB2 for i tables being accessed on the server are not being journaled. To correct the SQL7008N error, you need to start journaling your tables or change the isolation level to No Commit.

Tables may exist on IBM i server with character columns tagged with a CCSID value of 65535. Accessing these tables with DB2 Connect middleware will result in the EBCDIC hex representation being returned to the client. DB2 Connect does not have the ability to convert these columns tagged with CCSID 65535 into a character data string that's usable for a DB2 Connect client. There are two suggested methods to make this character data is converted into the proper encoding for a DB2 Connect client
1) Change the table's columns to the correct EBCDIC CCSID (typically 37 for US English customers). This can be done either through the ALTER TABLE statement or CHGPF CL command.
2) Create a view over the table which casts the column to the correct EBCDIC CCSID.

[FaStOnE]

Re: PHP and CCSID Conversion

Wow .. Thanks Boss!

Where did you find that though... I've been Googling all over ... well, she didn't know at the time <g> But, I was searching all over for something like that for 2 days now.

[littlepd]

Re: PHP and CCSID Conversion

Let me Google that for you.