Writing mixed text and packed decimal using JDBC and SQL to AS400 DB2

momi sabag:
Thank you for the comment.  I am not sure how to determine which way is the AS400 database set up - I do not know whether the text fields are varchar or char.  What is the best way to find out?

A few ideas come to mind.  Haven't tested any, though:

Write to a file containing the correct layout (packed field defined as it's own column), and then use the CPYF FMTOPT(*NOCHK).  This only works if the packed data appears in a fixed position.

If the data is in an unpredictable location, you can still use the intermediate file idea, but declare the entire record as binary fields per momi_sabaq's suggestion , and handle your own packing and ASCII(Unicode)/EBCDIC conversion, then copy from the intermediate file using CPYF *NOCHK

Another alternative would be to create an AS/400 stored procedure to build the combined field for you.  Trivial to do in a C or RPG stored proc, for example.

- Gary Patterson

Check out y EE profile: http://www.experts-exchange.com/M_4382324.html

Gary_The_IT_Pro:
Thank you for your comment.  
The CPYF option would not work too well as I access the database from an interactive application using JDBC and Java on Apache/Tomcat.  The packed fields are on the same location so that would not be a problem.  
I am creating and using stored SQL procedures but do not know how to write packed field from SQL - to the use of stored procedure calling another language may actually work.  I will try that.
Thanks again!

MPK1:

I can't be certain from what's posted so far. But, are you simply trying to execute INSERT and/or UPDATE statements against a table on the AS/400? You should be able to handle the packed field just like any other numeric type. No ASCII/EBCDIC conversion should be involved as far as the data goes.

I'm obviously missing something due to insufficient Monday morning coffee...

Tom

Tom,

If I understand the issue correctly, the packed data is not in a numeric field - it is embedded in a character field that also contains character data.

- Gary

@Gary:

Ah. Well, that would indeed be an issue. And the pseudo-packed/character field isn't CCSID 65535? (Or all character fields are 65535 which makes handling the exception field difficult...)

Yes, now after re-reading the opening statement, it makes sense. That is, the problem makes sense -- the field definition doesn't.

By handling as 65535, the non-packed portion could be ASCII/EBCDIC converted before the packed/hex characters are concatenated in. The entire field might then be transmitted without conversion.

Maybe.

Also, a stored proc does seem reasonable. The true character portion and the numeric portion could be passed in separately. The SP would assemble the packed bytes and concatenate them on.

That's about all I have to add. Looks like the question's being handled as well as the design allows.

Tom

@Tom and @Gary:
Gentlemen:
The packed field is defined in a text field (CHAR) and I cannot change that characteristic.  I tried various schemes to bypass the ASCII - EBCDIC conversion, but I failed in all.  The problem is that I have a packed date: 2091005 that should end up in EBCDIC packed as "20", "91", "00", and "5F" (speaking of hex presenatation).  
The packing in ASCII is performed using AS400PackedDecimal java class that generates the above hexadecimal values.  The problem is that when I store it in the database, it ends up scrambled.  
I am right now trying to approach it using overriding DDS (PF with LF) and should see if that works.  
I really appreciate your comments!
Regards,
Mark

Mark:

Although the packed characters are in a text field, the problem seemed to be that the field included other non-packed characters. E.g., the first four bytes might contain the letters "DATE" and the last four bytes would contain the four packed values. The total field length would therefore be eight bytes. This impression comes from your initial description "...a text field with some portion consisting of packed decimal data." The portion containing packed bytes would be the final bytes in my example.

Is that accurate? The field has mixed character representation?

Also, what date is represented by "2091005"? I would normally expect "20091005" or something similar. When packed, those bytes would be '02', '00', '91', '00' and '5F' -- five bytes in length. But maybe your date representation is custom or it was a simple typo.

Tom

Tom:
You are correct, the field is a mixture of text and packed data.  
The field is in a COBOL Presentation

01 combined_field.
 05 field1  pic x(2).
 05 field2  pic s9(7) COMP-3.
 05 field3 pic  s9(3) COMP-3
 05 field4 pic s9(3) COMP-3.  

Presentation of date is CYYMMDD (C=2 for 2000s, =1 for 1000s ).  So starting on position 3 of the text field, the packed 2091005 will show up as 20, 91,00,5F  (note that F means positive). Similarly, the first text positions are "01" represented by "F0", "F1"  in the EBCDIC.  Total length of the field is 10 characters.  

As far as I can tell, the machine uses Cp037 codepage and I am on a normal ASCII - standard Java (or POJO).  

This problem is quite a challenge.  Thanks for any thought and input!

Mark

 
It is custom and that is my problem.  

Mark:

You can _probably_ get things to work either by a pretty ugly concatenation of characters in your Java (which you started out trying) or by way of a SP to do the work on the server. You appear to have no choice but to keep the very unfortunate definition of the compound field.

However, given that the field is compound with packed subfields and that the system runs under Cp037 (CCSID 37), then the CCSID atribute of that specific column probably shouldn't be CCSID 37. IMO, that column ought to be CCSID 65535. That more correctly identifies the characteristics of the data values held in the column.

If there is an administrator, a DBA or anyone responsible for the table, it seems possible that a ALTER TABLE... ALTER COLUMN... CCSID 65535... statement should set the column definition without actually changing anything. Changing the column CCSID to 65535 essentially tells DB2 "This is binary data. Don't do things like ASCII/EBCDIC conversions." The data themselves in all of the rows won't change. And programs that work now should continue to work as always. Exceptions would be other ODBC/JDBC kinds of apps that were previously written to do what you're trying to do.

I suspect that a SP will be the eventually chosen resolution and I understand why. Just adding a potential resolution to keep in mind.

Tom

BTW, the ALTER TABLE... ALTER COLUMN... statement would only be run against that single column. Any other true text columns should remain as CCSID 37.

Tom

Tom:
Your last comment is very interesting.  I do not know if DBA would do this for me, but I can build the same table in different library and set the column to CCSID of choice.  Once I deposit record to that table, I can simply copy it to the main database using SP.  Once tested, I should be able to go to the DBA with the request.
I will let you know.
Thanks!
Mark

Mark:

Be aware that _if_ this done, you'll need to ensure that the first subfield, the apparent character digits, have the x'F0' thru x'F9' hex values. Like the remaining subfields, there should be no translation/conversion; so, you'll have to do it in code.

Tom

This worked, too.  The 'AA' literal translated properly into EBCDIC x'C1C1', and the packed numbers 1234567, 123, 123 all went through untranslated.

- Gary

INSERT INTO mytable (COMBINED) VALUES('AA' || x'1234567F123F123F');

                                              

1:

DDL for the table/column I used, for comparison to your column:

CREATE TABLE MYTABLE (
    COMBINED CHAR(10) CCSID 37 NOT NULL DEFAULT '' ) ;

Gentlemen:

This made it work:
update ahdmas set AUTHNODUP1 = ('01' || x'2090929F001F001F') where  
batdat = 2090929 and seqnum = 1  

Great appreciation to Tom and Gary!                                  

A briliant solution to my ugly problem.  

Happy to help.  It would be convenient to use String.format to  build your dynamic SQL.

String.format("update ahdmas set AUTHNODUP1 = ('%s' || x'%sF%sF%sF')",parm1,parm2,parm3,parm4);
 
                                                                                                             

                                              

1:
2:
3:

Mark:

Gary's comment...

> ...personally, I'd not make it a binary column, I'd define each of the subfields as a distinct column with the correct data type,...

I expect that you realize that that's technically the "correct" answer overall. A correctly defined database makes this stuff go away. I imagine that it wasn't as important when this was designed. But when remote clients get involved or even the files are merely accessed via a 'structured database language (SQL)', the assumption becomes that it's 'properly structured' at the start.

And also, if you're unfamiliar with the suggestion about [CPYF *NOCHK], it might be worthwhile to become familiar. It's a useful tool in tricky situations when quick recoveries are needed.

Tom