Data Transfer

Been forever since I used an AS/400.  You *can* get your data via FTP, correct?  Would it be possible to parse out the 'packed' files using Perl (or some such) to extract out your data and then load them into MS SQL afterwards?  Will most likely be much faster on the load, but you'll probably spend quite a bit of time developing the 'parsing' script/app.

Dave,
That is the major issue we have, no disk space so I cannot unpack the fields and then transfer.  That would be the route I would much rather take if it were possible

If there was a way to parse the packed fields I would be all about doing that, just dont know if its possible.

Hi
have you tried client access data transfer, from any 5250 screen you can initiate it.
Output to a CSV file then import into into your database.

That way it is the PC diks this is being used.

Dave

Dave,
Another great idea, but I did try that as well.  It was running for about 8 hours before I cancelled the transfer.  The notes in our ssytem completed quickly for 42M records, but the accounts 15M records took forever.  There are more columns in the account file.

How many packed fields are in each record?

"There are more columns in the account file"

if there are only a few packed fields then use client access to download & convert the packe fields along with a unique key and then match the details in the new database to the FTP'd file.

dave

There are about 130 packed fields.  

What is the CCSID of the accounts file (you can use dspfd ti find this out)

37 is the CCSID

Assuming  you can handle the remaining minor numeric inconveniences, Steve's method is very good.

I'm not an SSIS expert, but if you post your SSIS job we might be able to offer some specific suggestions for speeding that job up.  For example, on the OLE DB destination component, common advice is to adjust the commit size (try 10K-20K) and make sure and specify "Fast Load".

Using SSIS or Client Access File transfer requires far more in the way of PC server resources than a simple FTP transfer.   Also, with FTP, negative zoned numbers may be  a problem,  and the decimal point is implied, so you'll have to handle that for each field on import.

If you had the disk space, I'd just suggest that you use the CPYTOIMPF command to export your database file to a CSV in the IFS and then downloading that via FTP.  Is there no way you can temporarily free up some disk space to allow doing that?  

You could also try doing the CPYTOIMPF to a file in the QNTC file system (which allows you to access Windows file shares from the AS/400), but my experience is that QNTC file transfers don't set any speed records either.  Still might be worth a quick test.  Extract 1% of the file and time it.  Using the QNTC file system requires some configuration, however:  

http://www.itjungle.com/fhg/fhg031704-story04.html

You can also try some or all of these suggestions to improve the performance of the ODBC connection:

Pick a slow time of day, or even better, a time when you can gain exclusive access to the AS/400 and the SQL Server.

Try doing a simple SELECT ... FROM TABLE with no record selection, if possible.  Omit any unneeded or unpopulated columns to improve performance.

Make sure you have a fast, full-duplex local network connection between your SQL Server box and your AS/400, and make sure both machines have negotiated the fastest possible connection based on the adapters they have.  I've seen network cards set to AUTO that negotiated a slow connection or a half-duplex connection when a faster or full duplex connection was available, or that were manually configured for a slower connection.  This probably isn't a problem in your case since FTP runs fast, but just in case you are FTPing to or from a different system than your SQL Server, it is worth a check.

If permitted, avoid using an SSL connection on the data source (General-Connection Options) to avoid the additional overhead at both ends.

Data source - Performance Tab:

Once the query starts, find the associated server job (QZDASOINIT job), and make it a higher priority job (which means reducing the RUNPTY setting).  WRKOBJLCK *USRPRF on the user profile that you are running the ODBC connection under to find the particular job - it will be holding a lock on the user profile - then use the CHGJOB command to change the run priority.

Make sure that the QZDASOINIT (QZDASSINIT for SSL) job is running in a pool with plenty of memory.  Temporarily reallocate memory from another pool if DB page fault rates are high on WRKSYSSTS for the pool that your transfer is running in.  If auto-tune is on and memory is available, it will generally  move memory for you.

Some of the same basic tuning ideas  can be applied to a Client Access file transfer.

Of course, use care if making these changes while running other production workloads.

- Gary Patterson

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

It _might_ be most effective to create a VIEW that presents the packed columns in a character format. It shouldn't result in any significant addition to space usage and could be adapted/adjusted to handle most oddities that are likely to arise. Then, ftp the view.

Tom

Man that looks hard.

How about a "mount"?

CRTDIR DIR('/MOUNTED')

MOUNT TYPE(*NFS)                  
      MFS('10.10.10.10:sharename')
      MNTOVRDIR('/MOUNTED')      

CPYTOIMPF FROMFILE(YOURLIB/PHYISCAL)    
          TOSTMF('/MOUNTED/yourfile.csv')
          STMFCODPAG(*PCASCII)          
          RCDDLM(*CRLF)                  

No additional DASD used on the 400.
This should be easy to run.  The MOUNT will be the only thing to give you any trouble.
It is a UNIX kind of thing so a Widows guy should be able to handle it.

Steve Bowdoin