RE: using Packed- or Zoned- with other systems

Tony, 

I can't understand why SQL server would even need to know about packed or
zoned.  That's for the database to handle.  As far as MS is concerned, its
passing a NUMERIC data type.  That's all that's needed...  

This is the same as ASCII to EBCDIC.  That conversion happens under the
covers of the iSeries ODBC driver.  

Can you check which driver you're using for the DTS package?

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tony Carolla
Sent: Tuesday, May 02, 2006 5:07 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: using Packed- or Zoned- with other systems


I have a program that builds a data warehouse of sorts on our iSeries. Most
of this data was coming from the iSeries until recently, when we moved our
imaging to the Windows platform.  I am trying to get a simple list of
elements from an SQL server 2005 database into an iSeries table, using RPG,
DTS, or whatever tool I need.  This will be used by my RPGIV build program
to build the warehouse records for reporting.

My first attempt was to simply create a view on the SQL server with the
data, create a table on the iSeries, and set up a DTS package on the SQL
Server to pass the data from the view to the physical file.  The problem is
that the SQL server doesn't understand the format of packed or zoned fields,
and cannot map the fields from the view to the fields in the table.  I know
that, inside an RPGIV program, you can use the I and U data types, and these
correspond to INT and LONG types in the PC world, but how to accomplish this
when the destination is a table on the iSeries, and not a program?

I am asking for suggestions in accomplishing this.  The options I can think
of are:

1)  Turn the numeric fields into character in the SQL view, pass them as
char, then convert inside the RPGIV program
2)  Use CLI SQL to fetch the data from the view into a temporary table on
the iSeries
3)  Write a procedure/batch file to extract a CSV, and import to iSeries
table.

Any suggestions would be helpful.  Thanks!

--
"Enter any 11-digit prime number to continue..."