Originally posted by: SystemAdmin

I am just starting to learn DataStage TX and am struggling with how to move a directory of files to a new location, process those files with a PGP command line utility to encrypt/decrypt the files, archive the files and then use a more standard map to load the data into a database. I have created a type tree and a map that can copy files from an FTP folder to another directory but if multiple files exist they are concatenated in the output folder.

Does anyone have good examples with explanations or can any one direct me to documentation on getting Datastage TX to do tasks like this? I have not had much luck finding anything in about three days of searching and since I am so new with this tool I am having trouble understanding much of the content in this forum.

Ascential examples that I have seen so far do not cover this topic and all the Ascential examples lack a decent description how the examples work.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Let's split this up into several tasks:

1. Move files from 1 place to another.

Does this need to be done just the once, or scripted at certain times, or do you need an event to happen, every time a new file appears?

What is the source? Can the source files be reached with a standard file system or are the files only accessible via FTP.

I have a map that will take an FTP folder as an arguement, and transfer all the contents 1 file at a time to a local target folder. Is this what you are looking for here?

2. Encrypt/Decrypt PGP data.

The security option pack has a PGP adapter. Do you have this pack?

If you really want to do it at the command line, have you tried the shell script/batch adapter? Would you like an example of using this?

3. Archive files

Have you looked into the Archive (.TAR or .ZIP) adapters? Would you like an example of using this?

4. Insert into a database

Do you have any experience of the Database Interface Designer? To what database do you need to connect? Can you be more specific.

Overall it sounds as though you may benefit from a training course, or just play with the product, and post individual questions to the other members of this board as and when you get stuck.

Hope this helps.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Paul,

Thanks for your response. Let me give you the specifics of our environment and what we need to accomplish.

We have multiple ftp folders that our customers put encrypted files into. Our customers will usually deliver two files a day but there could be more. We need to either check for files at a regular interval or if possible fire a process off when a file appears in any of the FTP folders. The FTP folders are on an FTP site outside our firewall so they are not available locally. We have a table of all our customers and their FTP and local file paths so ideally we would query this table and use the results to check for files. I know I can setup a map or a series of maps to be run at regular intervals but I am not sure about real-time file processing with the FTP adapter. Is that possible and how is it done?

An important question I have related to the FTP site and files in general is what if the file is open or in the process of being written to? Will the DataStage TX product realize that a file is being written to and leave it alone instead of trying to process it and thus creating an error? This is especially important with FTP since some of our customers upload large files at slow speeds.

We need to move any existing files in the FTP folders to a local customer folder, decrypt the files using PGP, move the original encrypted files to an archive folder, process the unencrypted files to a database and then archive the unencrypted files. Your map and tree that will take an FTP folder as an argument, and transfer all the contents one file at a time to a local target folder sounds like exactly what I am looking for since I want to use a table of customers to drive the retrieval of files.

I am not sure if we have the security option pack has a PGP adapter. We just purchased the extended DataStage TX v7.5 and I need to check to see if it was included and available with this version. Even if we have this module I would still be extremely interested in any examples using the shell script/batch adapter. We do have the command line version of PGP since we were anticipating the need to use the shell adapter but I just have been unable to find any good examples or details on using the adapter. The information will also be extremely useful for any other custom type of file processing we may need to do. Examples of using the zip adapter would also be useful. I found some information on this but I am confused by the relation of the type tree and the map when it comes to file operations. Trees make sense when loading a file contents to a database but I am confused in how they relate or should be built for file operations like decrypting or zipping.

I have used the database interface designer but I have been unable to get it to see stored procedures or views. Any ideas on what would cause this? I have been able to extract data from a database but I get only one record even when the result set includes many records. I believe this has something to do with setting the map to do multiple values.

One key concept I really need to grasp is parameters and using them to control map operation. I can see from forum the posts that this is a powerful concept but the posts and the product documentation do not describe how to do this well enough for me, at a beginner level.

We have one training seat with our DataStage TX version but someone else will be going. We are trying to get the training documentation ahead of the scheduled class date. I usually have no problem taking good documentation or samples and learning a tool or programming language. In fact I prefer this method of learning. The DataStage TX product is so different from any other tools I have used and the documentation that comes with the product is not very detailed, especially when it comes to processing files (not file content) so I am struggling a bit. I am sure if I can get some whole examples (type trees and map files) or some good narrative on how to do these tasks I will eventually make sense of it and even start helping others.

I appreciate your or anyone else’s help getting me up to speed using this product.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Hi Frank,

u:23606496c3FTP[/u:23606496c3]
The FTP adapter does not do any real time processing - you'll have to set your map to be time driven and then check for messages on the FTP server on a regular basis (e.g. every 5 minutes).

The FTP adapter won't know that the file is being written to by another process and therefore it may pick up an incomplete file (if there is nothing to stop the file from being processed it will be processed). However there are ways round this - e.g. the default AIX FTP server locks files while they are being written to, ProFTP can hide files while being written (using the HiddenStor directive) - so check the documentation for the FTP Server.

The most common method to fix this problem is to have the FTP client rename the file after deliver e.g. order100.tmp --> order100.dat. DSTX should be configured to only look for *.dat files.

u:23606496c3PGP[/u:23606496c3]
the command line depends on the flavour of PGP adapter. in our map we:
1) save the encrypted data to a file (using PUT("FILE",filename,data))
2) decrypt using (GET("SHL", "-CMD ""pgp -f < """")
3) then delete the file using EXIT("rm", filename," ")

(Ideally it would be possible to do it in one step by GET("SHL" doesn't accept a third parameter.)

however the built in PGP adapter seems to work fine - you should use this if possible.

u:23606496c3ZIP[/u:23606496c3]
Zip should work in much the same way, use the built in adapter if possible.

u:23606496c3DB[/u:23606496c3]
Possibly you need to set "Access user tables/procesdures only" to 'no'.
using the default options, a simple query should return multiple rows to the map - what does your database trace file say ? (check what your fetchunit is in your input card)

u:23606496c3Paramters[/u:23606496c3]
Both maps and adapters can have parameters. The documentation is pretty good but you'll probably need to do some experimenting to get the results you want. Do post any specific questions on parameters that you have.

Cheers,
Olly.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender