Update existing or insert new rows

[abhilashnair]

I am using a server job in the following format

Seq File---------->Transformer-------------->ODBC Stage

Data is being loaded from seq file to a DB2 table. I am using the option "Update existing or insert new rows" in ODBC stage

The metadata format is exactly same for seq file and table. There is only one key field.

i am getting 34 warnings in the director log.

DSD.BCIPut call to SQLExecute failed. 2 rows affected.

the same warning 34 times

Exactly 34 records are less in the target table than the source file.

As far as my understanding when we select Update existing or insert new rows, if there is nothing to update then it should automatically insert those values into that table. I agree it will throw warnings because Datastage logs any error returned from the Database. But again why it is not inserting those 34 records.

Please help.

[ray.wurlod]

It usually means that the database has rejected those records, perhaps because some constraint has been violated. In the Transformer stage immediately prior to the ODBC stage add an additional output link and mark it able to handle rejects. Create three columns there (all VarChar).
SQLState - VarChar(20)
DBMSCode - VarChar(20)
ErrorText - VarChar(4000)
Drag all the rows from the input link to this reject link.
Derive the three columns created above from eponymous link variables from the output link to the ODBC stage. This reject link will now tell you precisely why the rows were rejected.

[shilpa79]

For the Inital run choose the option Inserts and load the data from FF.
After that make a self lookup with the Target table and compare the columns from source and lookup colums . On the target side there will be two output links ,one for insert and another for updates. In update check the key columns and if u have load date select only tthese columns and update the table.
To check which records are rejecting put one more Output link for rejections.

In the constraints apply the logic :

If records are found and recordcount = 0 then Update
If records are not found and recordcount= 0 then Insert
If recordcount>0 then rejects

Your design will be:


self lkp DB2 target tbl


FF ---------->Xform-------------->Db2 table (Insert) and more link for update

Hope this should work for U

[abhilashnair]

Actually there was no primary key defined on the table. So there were some duplicates which caused this error. Now the table has been assigned a PK. now it is fine