ODBC Connector - Warning - Insert Then Update - HANA

[rbk]

Dear all,
This is regarding a query which has been discussed a few times, but the exact fix seems to elude me.

Scenario:
We are trying to load a HANA table with the write method as "Insert Then Update".
The HANA table belongs to a package. Hence the table name shows up as "SCHEMA_NAME"."PACKAGE_NAME::TABLE_NAME". (with the double quotes.)

Issue:
The job seems to work when the generate SQL has been set to 'YES'.
But when we set it to 'NO' we immediately get a barrage of warnings.

Code: Select all

Message Id:
IIS-CONN-DAAPI-00063

Message:
Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed

I had used the query from the director which datastage had generated and yet I am receiving the same warning.
Kindly help me with any pointers regarding resolving this issue.

Additional Information:
1. We have 24 columns in all, with 4 audit columns, two for create id and timestamp and another 2 for update id and timestamp.
2. We have 3 keys, but they are not actually available in the table. Can this be an issue ?
3. The generated sql is updating 21 columns with the exception of the three key fields. (21+3)
4. The insert statement inserts all 24 columns.
5. The source is a dataset having all the 24 columns. The jobs merely has a dataset leading to a copy and then to a ODBC stage. Rejects are captured as well.
6. Updates are happening properly despite the warning.

[chulett]

I have to ask... why not simply leave the Generate SQL option as "Yes"?

Other than that, on your additional information points:

1. Okay
2. No clue what that means. Not available in table?
3. That's how an update works, the key fields are bound into the where clause
4. Again, that's how it works
5. Okay
6. Okay

So, you want to supply your own SQL for some reason and don't know how to deal with the warning you posted? Were there other warnings? I don't think one warning can be considered a barrage, hence the question.

[rbk]

Hey Hi Craig,
Thank you so much for your response.

The reason I have to change the Generate SQL option to 'No' is that (my intended requirement) all the fields except the Create Id and create date have to be updated whenever we encounter an update. So for the first time encounter a new set of keys, we insert the current date as the create date and when we encounter an update for the same set of keys we update the updt_dt and the other fields but the create date will point to the date the record first entered the system. So going forward I would be modifying the update statement to remove the create date and set the update date to current date instead of picking up the values from the source.

We are currently running on a GRID setup, hence the same warning gets generated for each node that the job is running on. So we have at least 8 warnings and my job is set to abort after just 3 warnings.

What I had meant was that the three fields are not set as Primary keys, they are of course available in the table (It was hilarious when I re-read my own statement ) We do have unique indexes set up on the three keys mentioned.

Please let me know if you need any more information.

[chulett]

I've never been a fan of those "dual action" methods, much preferring to do the determination myself of what was needed and then crafting the appropriate DML for each situation. And just to be anal, all a "key" field means is it can be used in a where to identify records to update... they don't need to be primary keys or even identify a unique record. We tend to keep multiple sets of metadata for a target table with different "keys" marked if we have different update scenarios to support and they don't always include all of the fields. For instance, you could craft one without the creation fields so they are left alone. Unfortunately, none of that is an option for your combo action.

Perhaps you could remove them from the metadata so they're not in the update and then leverage a default value in the database on the insert? Not sure what else to suggest at the moment, I'm sure others will be along in a bit and chime in as well.