Error using surrogate key in transformer

anu123 · Post by **anu123** » Thu Feb 06, 2014 2:59 pm

Hello All,

I am getting the following error when I am trying to generate Surrogate Key in the transformer. I am using function NextSurrogateKey().

xfmr_data,0: Operator terminated abnormally: received signal SIGSEGV
main_program: Step execution finished with status = FAILED.

I am using DB sequence. We are using DB2 database, version 10x, I guess, not sure. But definitely above 9. I have provided the sourcename (UDP.SRA_POST_SEQ - This is the Sequence name), User name, password, Database name.I have also verified APT_DBNAME.

If I haven't tried to generate Surrogate Key, Job runs fine and I am able to capture data in a dataset. I am facing this issue when trying to generate a surrogate key value.

Parallel Job.
DS version: 8.1.
Unix

Please shed some light. Do I need to provide any other details.

Thank you every one.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 06, 2014 3:53 pm

Have you filled in the state file (or sequence) information on the Surrogate Key tab?

chulett · Post by **chulett** » Thu Feb 06, 2014 5:00 pm

How was the DB2 Sequence created? From what I recall, the stage has to create it which means a separate one-time job to do that. From what I recall.

anu123 · Post by **anu123** » Fri Feb 07, 2014 7:44 am

ray.wurlod wrote:Have you filled in the state file (or sequence) information on the Surrogate Key tab?

Ray, Yes I did. I have selected DBSequence for Source Type, I have given the table Sequence name in Source Name, Database Type - DB2, Provded Username, Password and Database Name. The database is on the same server, so I haven't provided Client Instance Name.

anu123 · Post by **anu123** » Fri Feb 07, 2014 7:49 am

chulett wrote:How was the DB2 Sequence created? From what I recall, the stage has to create it which means a separate one-time job to do that. From what I recall.

Chulett, At the database level it is an auto incremented value by 1. Generally if I am writing a sequel to insert values into Databse I am using SequenceName.nextval. I thought if I have given the sequence name in the surrogate key tab in the transformer and used NextSurrogateKey() in the column derivation in transformer, then it will be same as using nextval function. Please correct me if I am wrong.

Thanks.

chulett · Post by **chulett** » Fri Feb 07, 2014 8:36 am

The documentation notes some 'extra steps' are required for DB2 usage of that function:

7. If the database type is DB2, complete some additional steps:
a. In the Database name field, type the name of the database. If you leave this field blank, the stage uses the name that is specified by the environment variable APT_DBNAME, or by the variable DB2DBDFT if APT_DBNAME is not set.
b. In the Client instance name field, type the name of the DB2 client instance. This field is required for a remote database.
c. In the Client alias DB name field, type the name of the client alias database on the remote server. This field is needed only if the alias database name is not the same as the server database name.

FYI

anu123 · Post by **anu123** » Fri Feb 07, 2014 2:49 pm

Chulett, Thank you for searching that for me.

But I have seen those step and verified all those. I have also mentioned those details in my first post, that DB is on same server and I have verified APT_DBNAME and also provided the database name while running the job.

Is there any other checks I need to do.

Thanks.

chulett · Post by **chulett** » Fri Feb 07, 2014 2:58 pm

Well, that's good.

I am still under the impression that you need to let DataStage create the sequence. In any case, why not ping your official support provider, see what they say regarding your error?

anu123 · Post by **anu123** » Tue Apr 01, 2014 1:56 pm

Our DB2 is on mainframe and we are connecting through a gateway. So I guess we can't use it atleast in 8.1 version.