DTS and AS/400 1

[Ditch2]

I'm trying to set up a DTS package on an MS SQL Server that will run at timed intervals to grab data out of an AS/400 table. I have an ODBC Data Source from HIT Software that talks to the AS/400.

Only a small number of records are actually added to the AS/400 table for a given time period so I'm trying to use a JOIN query in the DTS to give me only the stuff the MS SQL Server doesn't have. I can't figure out the syntax for using an AS/400 table and a SQL table in the JOIN query. I really don't know if this is even possible.

If it were two SQL tables the query would be:
SELECT * FROM Table1 LEFT JOIN Database1..Table2 ON (Table1.CommonField=Database1..Table2.CommonField) WHERE Database..Table2.CommonField IS NULL

This query would return only the data not common to the two tables, in my case any new records added since the last DTS. The Database..Table.Field notation doesn't seem to work. I get a DTS error message that "Token . was not valid" How do you do call the Database/Table from a completely different server, in this case the AS/400 in a DTS query? Any suggestions?

 

[Jimrv6]

Can you perform a simple select from the table? I populate a warehouse from an AS400 each night but I simply drop the SQL tables first and read all records from the tables on the 400. There is something about the select that your 400 doesn't like. I am using the IBM Client Access for the connection.

 

[TomSark]

One thing I can remember about DB2 is that it can't support the use of NULL values, a severe limitation in my opinion. Perhaps that's the problema here on the 400 side... I'm not sure, but I would check that. I'm not an IBM geek... I would also ensure that the 400 supports ANSI92 SQL.


Tom

 

[acaswell]

Perhaps it didn't in the past, but DB2 does now support NULL values, if the field is described in the DDS with the ALWNULL keyword.

To specify an AS400 DB2 file, try using the syntax LIBRARY/FILENAME instead of LIBRARY.FILENAME. I don't know how your HIT software works, but in SQL Server using Client Access Express to establish an ODBC connection, there is an option that lets you choose one of these two syntax options to specify the file (table) name.

 

[TomSark]

Are you getting communication links failures when this is happening?

Tom

 

[chakri99]

I don't know whether this will work.

It worked for me for similar error.

I used database2.filenam2.filename2 The second filename is for the member name as db2/400 would look for that file as well as member name. You may use filenam2 or even *first as the member name

Give it a shot and let me know

Chakra

 

[SpriteInMe]

I have been trying to use DTS and ClientAccess. When I create a package it runs fine. Selects, writes, etc. As soon as I set this up as a Scheduled task it no longer works.

Have you been experiencing the same problems?

Thanks!

 

[SpeedOLight]

I'm assuming you have a linked server setup to access the AS/400? If so, you might be able to use the OpenQuery command, although I've never used it for joining between two different DBs. I've only used it with Selects, Deletes, Updates on the AS/400.

Format of the statement would be:

Select *
From OpenQuery( AS400LINKEDSERVERNAME, 'Select * from AS400file' )


Delete
From OpenQuery ( AS400LINKEDSERVERNAME, 'Select * from AS400File' )

I haven't been able to figure out the correct dot notation for referencing a table on the AS/400, so I use the OpenQuery command instead. May be worth a shot..

Regards...Marc
Independent Software Consultant