Datatype Not Supported

mark.hutcherson · Post by **mark.hutcherson** » Tue Jul 01, 2008 11:45 am

Hi, I need some help on an issue I have. I hope this is in the right forum, if not please let me know where I should post.

I am using Datastage 7.5.2 to create some elt packages and have run into a problem. I recive the following 2 fatal error statements:

1) main_program: Datatype Not Supported

2) AAP_AS400_Product: Field: ID in the input not found in db table: AAP_AS400_Product and drop option was not specified
Field: Name in the input not found in db table: AAP_AS400_Product and drop option was not specified

AAP_AS400_Product is the name of the SQL Server Target table. ID and Name are 2 fields in the ODBC Enterprise Stage and are defined in the columns of the stage.

I am transfering data from an AS/400 DB2 database to a Windows SQL Server DAtabase.
For the source use a DB2/UDB API Stage. I am using a SELECT SQL statement with the field names to create a data set.
This dataset is sent to a Transformer Stage where the fields are mapped with no processing.
The target is an ODBC Enterprise Stage.
The original tables have 47 fields. The Source fields are Char and Numeric. The Target fields are Varchar and decimal.
I have created a package with the same tables using only 2 char fields for both the Source and Target stages.
Both of the packages give me the same error.

Thanks of any help
Mark Hutcherson

John Smith · Post by **John Smith** » Tue Jul 01, 2008 11:37 pm

Are you able to View Data in the DB2 API stage?
Did you specify the metadata for your tables?
Have you attended the Data Stage training course?

mark.hutcherson · Post by **mark.hutcherson** » Wed Jul 02, 2008 9:47 am

Yes, I am able to view the data correctly in the DB2 API Stage.

I have supplied the metadata for both of the stages.

I have been through the DataStage Essentials V8 training. But I am new to DataStage Development.

OddJob · Post by **OddJob** » Wed Jul 02, 2008 9:52 am

Do you have RCP enabled for the stages in this job?

OddJob · Post by **OddJob** » Wed Jul 02, 2008 10:05 am

If you have RCP enabled, and ID and Name fields are part of the source table, and they're not being explicitly dropped in a Modify stage then they'll make it all the way to the DB load stage and it may try to use these as target fields. If the target doesn't have ID and Name fields then you may get this problem.

John Smith · Post by **John Smith** » Wed Jul 02, 2008 5:31 pm

which two columns are defined in your source? Is it Name and ID?
What you want is to explicit define the columns you are sourcing and the columns you want into the target.
Make sure that the datatype for these columns are exactly what is defined in the database.
As the previous poster added make sure that you had not turned on RCP.

mark.hutcherson · Post by **mark.hutcherson** » Thu Jul 03, 2008 1:40 pm

Our Adminsitrator seys that RCP is not turned on.

ID and Name are the fields in the target database and I have them defined exactly as they are in the SQL Server database.

ID is VarChar 16
Name is VarChar 100

The source fields are also defined exactly as they are on the AS/400 DB2 Database.

PRITM is Char 8
PRDSC is Char 30

I then map them in my transformer stage. PRITM to ID and PRDSC to Name. It is a straight mapping with no logic involved.

John Smith · Post by **John Smith** » Thu Jul 03, 2008 5:09 pm

RCP can be turned on within the job. Check it in the job itself. Even though RCP is not enabled for the whole project it can be turned on by a checkbox within each of the stages.

Just make sure that you are pointing to the correct target SQL SErver database. The error you are getting seems to suggest that DS cannot "see" those fields (ID and NAME) in the target database.

Just to confirm it's a problem with the ODBC stage, make a copy of the job, rip out the ODBC stage and replace with a PEEK stage. See if the job runs. Then replace that with the DRS stage and see if that works.
With the ODBC stage, it's bit more fiddly and unless it's configured properly in your unix server (from memory i think it's the e .odbc.ini fie and uvodbc).

mark.hutcherson · Post by **mark.hutcherson** » Mon Jul 07, 2008 7:30 am

I was told that RCP is not turned on inside the job.

I tried the Peek stage and it worked correctly.

Here is the kicker. The reason I was using the ODBC stage was because the first package I created used the DRS stage. There was an issue with the DRS stage truncating the last character of all the alphanumeric fields.

I used a Peek and the data that is being sent to the DRS stage is correct. Somehow the DRS stage is chopping off that last character when sending it to the SQL Server.

Our administrator is looking into it but any suggestions would be appreciated.

John Smith · Post by **John Smith** » Mon Jul 07, 2008 4:59 pm

You might need a patch from IBM for the DRS stage problem. Which version are you running? Please log call with IBM because what you encounter is a bug.