Error connecting to AS400 file

gaya3 · Post by **gaya3** » Fri Dec 11, 2009 7:22 am

Hi,

I get a fatal error when I try to run a DataStage job connecting to a AS400 file. The job is trying to read from a AS400 file and write to a hashfile. The error message is not very clear. It shows

HashfilestageName.IDENT3: |-100|.

This error message comes only with a new library file created in AS400. It works fine with an already existing AS400 library.

Any thoughts on what the reason could be?

Thanks!

chulett · Post by **chulett** » Fri Dec 11, 2009 7:34 am

Post your job design. IDENT3 would be an "invisible" transformer inserted between two passive stages by the compiler, however that can't be the only message that was logged, right?

gaya3 · Post by **gaya3** » Fri Dec 11, 2009 7:43 am

Hi Chulett,

There is no other error message even when I reset the job. It just shows this message. Any clues on how to attack teh problem?

chulett · Post by **chulett** » Fri Dec 11, 2009 7:45 am

Perhaps by... posting your job design?

gaya3 · Post by **gaya3** » Fri Dec 11, 2009 7:50 am

It is a very simple job.

DB2 Stage --> Transformer --> Sequential File

Even when I try to view data in teh DB2 stage I am not able to see the data properly. Sometime it throws a DataStage repository error message and at times it shows junk values in all columns.

gaya3 · Post by **gaya3** » Fri Dec 11, 2009 8:03 am

This is the error message shown when I try to view the data, if it helps

testjob.DB2_UDB_API_0: /?( and one blank space)

chulett · Post by **chulett** » Fri Dec 11, 2009 8:10 am

That cannot be the complete design. What happened to "HashfilestageName"? And there's no way it would add an "IDENT3" stage with that design.

Scroll around and make sure there's no "extra" bits off screen somewhere that you don't know about. Worst case, export and re-import the job, if there's some kind of internal corruption that will clear it up.

gaya3 · Post by **gaya3** » Fri Dec 11, 2009 8:14 am

I am extremely sorry to have confused.

Since the original job was aborting I designed a new job with just the three stages and the message in my last post comes when I view the data in the Db2 stage itself.

The error message that comes in the job log (director) for this simpler job is
testjob..Transformer_3: |-100|

chulett · Post by **chulett** » Fri Dec 11, 2009 8:25 am

So, you've hijacked your own thread and completely changed the topic we are discussing? Nice.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 11, 2009 2:31 pm

-100 usually means "no more data". Is the file on the AS/400 empty?

gaya3 · Post by **gaya3** » Mon Dec 14, 2009 4:31 am

The files do have data. From more analysis, when I try to query just a single column which has numeric data, I am able to see the contents from the same file/AS400 library. The issue is only when I try to view data from a character column.

I get two kinds of error which keep changing at random. Either it shows some records with junk values, or it throws a DataStage Repository Interface error with the following error message.

JobName..Db2_UDB_API_0: /?(one space character)

ray.wurlod · Post by **ray.wurlod** » Mon Dec 14, 2009 3:02 pm

Methinks someone has corrputed DS_JOBS and somehow "converted" it into a Type 1 file. Can you please look into the DS_JOBS directory in this project and report what's there, including hidden files?

Code: Select all

ls -la DS_JOBS

gaya3 · Post by **gaya3** » Wed Dec 16, 2009 7:40 am

No Ray, this happens even in other existing projects.

To add more information from our analysis, the source database codepage is Latin -2 and the DataStage default is Latin -1.

We are trying to set the codepage corresponding to Latin-2 which is 912 to the environment variable $DB2CODEPAGE in the job parameters. We are unable to set it at the project level, because the same jobs would be processing data for more than one country with different codepages.

But still the error is not resolved.

Can you suggest any reasons?

chulett · Post by **chulett** » Wed Dec 16, 2009 8:16 am

So how are you 'trying to set the codepage' in the job? You can do it at the project level - set it to $ENV there, then add it to and override the value in your job.

gaya3 · Post by **gaya3** » Thu Dec 17, 2009 12:45 am

Hi Craig,

These are the steps followed to set the codepage at the job level.

Add the environment variable $DB2CODEPAGE in the job parameters and set the default value as 912. From the NLS tab of the Db2 stage, select the option IS08859-2.
This is because, the AS400 library from which we are trying to read the data has its code page as Latin-2.

Even after these settings are made, job aborts and this is the error message:

test..DB2_UDB_API_0: [IBM][CLI Driver] SQL0332N There is no available conversion for the source code page "912" to the target code page "500". Reason Code "1". SQLSTATE=57017

Unable to connect to DB2 server <AS400 server name>.

When I tried running the job with thedefault value as 1208 and the DB2 stage NLS tab option as UTF-8, it is running successfully. This is what I see from my experiments, but I am not sure if it is a good/correct option to choose a unicode setting to read data from a different character set.

Note: We read data from AS400 libraries from different countries, this is the reason why we hesitate to make any changes at the project level (which is common to all the countries)

Can you please confirm if my second approach is safe to take?

Thanks!