September 1, 2005 at 4:07 pm
Hi,
I need to transfer data from a SQL Server 7.0 table to a AS400 DB2 table. I use the DTS Export wizard and is having difficulty with numeric fields.
The fields of varchar type were transferred fine. The numeric fields in DB2 table (data type was set to "numeric" both in the SQL server table and DB2 table), however, only received zeros. I tried data types numeric, int, varchar, sql_variant and money in the SQL server table. None worked. (data type on the DB2 side is not supposed to be changed) The value of db2 table field was always 0 after DTS execution. I am becoming desperate.
Any advice?
Thanks a lot!
September 2, 2005 at 8:08 am
I remember uploading to an AS/400 from SQL2000 without much trouble (never used SQL7), but we don't have the AS/400 now so I can't test anything. Are you loading into an existing AS/400 file ? How about letting DTS create a new temporary file on the AS/400, and let it figure out how to format the data. Then perhaps you can map the data from your temporary AS/400 file to your live AS/400 file ??
I seem to remember CCSID formatting being involved in data mappings - sometimes having to change the value .... may or may not be relevant.
P.S. ... Posting this in the right forum, such as DTS might get more responses.
September 2, 2005 at 10:40 am
Hi-
Can you just change the data type on the DB2 DB temporarily to char or varchar and see what happens? Then change it back once the data is in.
September 2, 2005 at 11:25 am
Thank you for the advice. I tried. But, no, DB2 doesn't allow me to change table data type from varchar to numeric, vice versa. Not even with an empty table.
September 2, 2005 at 11:25 am
Assuming that DB2 is another name for the native AS/400 environment, then you can't just change data types of files. What I would try first is use DTS to dump the data to the AS/400 in whatever manner works, letting DTS create the AS/400 file and pick the field data types. Then copy that data from the uploaded file ("table" in SQL speak) to another file using something like:
CPYF FROMFILE(SQLUploadData) TOFILE(DB2Data) FMTOPT(*NOCHK)
September 2, 2005 at 11:35 am
It used to be a real pain to change the structure of files ("tables") on the AS/400. Then it got a bit easier, and if you needed to add a field, you go into the DDS, add the field, and then use the CHGPF command. You might be able to change some field attributes the same way, but you'd have to experiment.
What is the data type in your SQL tables and what is the data type in your AS/400 tables that are causing the problems ?
September 2, 2005 at 1:39 pm
Hi, Homebrew01/Jeff,
Following Homebrew01' idea, I did some tests and would like to share the findings with you:
these data type mappings from SQL Server 7 to AS400(i5 v5r3) Db2 work:
(sql server) numeric -> (db2) Integer, numeric -> varchar(n), money -> dbl
money ->float, varchar(n)->varchar(n).
these won't work:
numeric, money, integer, or varchar(n) --> numeric, money -> decimal, numeric -> decimal.
But a "numeric" type field in db2 table can accept varchar, integer etc values from other db2 tables. Just not from SQL Server table. So creating a temporary db2 table or changing DDS as you suggested, are all feasible solutions, maybe the only solutions-- "Numeric" type in DB2 is compatible with very few(if any) SQL Server types.
Also, the automatically created table by SQL server DTS is not necessarily correct. You might need to change the CCSID (37) and data types to be compatible with DB2.
Thank you indeed for both of your inputs. Very helpful.
Star
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply