Question

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

Asked by: MPK1

The problem is that the target field on AS400 DB2 is a text field with some portion consisting of packed decimal data.  I can use AS400PackedDecimal from the AS400 toolkit to pack the fields in Java, but writing it (using setBinaryStream method withing prepared statement), but the store will still scramble the results.  

Is there method where I can suppress the ASCII to EBCDIC conversion?

byte[] inputArray = ... //loaded elsewhere
ByteArrayInputStream istr = new ByteArrayInputStream(inputArray);
prep.setBinaryStream(9, istr); //prep is the prepared INSERT or UPDATE statement

                                  
1:
2:
3:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-10-04 at 15:34:12ID24784152
Tags

AS400 Toolkit

,

JAVA

,

AS400 SQL

Topics

Programming for iSeries / AS400

,

DB2 Database

Participating Experts
3
Points
500
Comments
22

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Connecting to DB2 on AS400 (CFMX)
    I am going to be connecting ColdFusion MX proffessional on and NT server to a DB2 database on an AS400 machine. Are there any hurdles that I should be aware of before I start? Thanks, Scott
  2. Linked server to DB2 on As400
    im having a problem creating a linked server to db2. we dont do SNA-- we have an AS400 with V4r5, and DB2 6.~~; and am running SQL Server 2000-- i think that the main problem is im having problems writing the OLEDB connection string for this-- because all of the resource i ...
  3. Connecting to DB2 on AS400
    Hi All, I am trying to connect to a DB2 on AS400 using ASP. I have installed IBM AS400 Client Access Express. I tried exploring it but to no valid. Please kindly provide me the details of the configurations to be done or include anything that you think I should be aware. T...
  4. DB2 vs PF (both on AS400)
    Hi, I used to work with Physical Files (PF) on AS400 and this is first time when I may have to work with DB2 tables on AS400. I need to pull data from As400 to windows environment (this could be .net or Java) over ODBC and I have an option to use AS400 physical files or DB2 t...
  5. Moving data from AS400 to DB2
    hi, whts best and simple way to move data from AS400 to DB2 Thanks, Bobby

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: momi_sabagPosted on 2009-10-05 at 01:02:10ID: 25493183

you can try working with binary values and then no ascii to ebcdic conversion should take place
try to define the column in the AS400 database as varchar for bit data

 

by: MPK1Posted on 2009-10-05 at 07:31:12ID: 25495609

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?

 

by: Gary_The_IT_ProPosted on 2009-10-05 at 07:38:10ID: 25495679

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

 

by: MPK1Posted on 2009-10-05 at 07:52:49ID: 25495833

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!

 

by: tliottaPosted on 2009-10-05 at 11:23:07ID: 25497981

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

 

by: Gary_The_IT_ProPosted on 2009-10-05 at 11:35:47ID: 25498131

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


 

by: tliottaPosted on 2009-10-05 at 12:39:03ID: 25498790

@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

 

by: MPK1Posted on 2009-10-05 at 13:11:46ID: 25499165

@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

 

by: tliottaPosted on 2009-10-05 at 14:37:04ID: 25500048

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

 

by: MPK1Posted on 2009-10-05 at 14:49:54ID: 25500153

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.  

 

by: tliottaPosted on 2009-10-05 at 17:36:17ID: 25501111

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

 

by: tliottaPosted on 2009-10-05 at 17:38:41ID: 25501121

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

 

by: MPK1Posted on 2009-10-05 at 18:08:58ID: 25501193

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

 

by: tliottaPosted on 2009-10-05 at 20:22:49ID: 25501675

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

 

by: Gary_The_IT_ProPosted on 2009-10-05 at 23:08:25ID: 25502186

Mark,

That's essentially what I was suggesting in comment 25495679 above.  Create a table with the correct attributes (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, then just insert into my new  properly-defined table,  then do a binary copy [CPYF *NOCHK] to the target table with the combined field.  You could call the system supplied stored procedure QCMDEXC to execute the CPYF command.

I have an even easier idea that you can quickly try, though:

I ran the following SQL statement from Run SQL Scripts on a V5R4 machine, and it inserted the untranslated hex value that I specified into a 10-byte character column.

Note that as of V5R3, the JDBC connection property "translate hex" is used to select the data type used by hex constants in SQL expressions. "Binary"  indicates that hex contants will use the BINARY data type. "Character" indicates that hex contants will use the CHARACTER FOR BIT DATA data type. The default setting is character.  

This only works with "translate hex=character" (the default).

In your program, you just build a dynamic SQL statement containing the hex literal you want to write to the underlying file.  Granted, this means a little work to construct the hex literal, but it eliminates the need to create a stored proc or the overhead of creating a duplicate table and copying records.

- Gary Patterson

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

INSERT INTO myTable (combinedField)  VALUES(x'F0F01234567F123F123F')

                                              
1:

Select allOpen in new window

 

by: Gary_The_IT_ProPosted on 2009-10-05 at 23:13:16ID: 25502198

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:

Select allOpen in new window

 

by: Gary_The_IT_ProPosted on 2009-10-05 at 23:22:16ID: 25502224

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

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


 

by: MPK1Posted on 2009-10-06 at 05:30:43ID: 25504220

Gentlemen:

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

Great appreciation to Tom and Gary!                                  

 

by: MPK1Posted on 2009-10-06 at 05:48:14ID: 31636997

A briliant solution to my ugly problem.  

 

by: Gary_The_IT_ProPosted on 2009-10-06 at 13:41:40ID: 25509736

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:

Select allOpen in new window

 

by: tliottaPosted on 2009-10-06 at 13:41:41ID: 25509737

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

 

by: MPK1Posted on 2009-10-06 at 15:22:51ID: 25510554

Gary & Tom:
I fully agree with your assessment of what should be an architecturally correct solution.  This approach has been developed in mid 80's by yours truly and it sounded as a good idea at the time.  The disk space considerations played quite a role back then especially for the multi-million record files.  The application hit over 9Mil records in one table in some installations.
The solution is now fully implemented and encapsulated so that no sensibilities are offended.  
I used the CPYF in the past but it was not appropriate for this approach - this processing occurs interactively and waking up the AS400 utility would probably cost me some response time.
Thanks for your help.  

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...