How to write Pre SQL in ODBC stage

palak08 · Post by **palak08** » Thu Sep 15, 2011 7:02 am

Hi All,

I am using Datastage with Netezza combination. To connect to netezza, I am using ODBC connection. When I use ODBC stage, I can not see "Pre and Post SQL" options.

Is there a way to use this feature?

Thanks in Advance for help.

chulett · Post by **chulett** » Thu Sep 15, 2011 7:14 am

I don't believe the Server ODBC stage supports that... the ODBC Connector and Enterprise stages do, however. What do you need to do before/after sql?

palak08 · Post by **palak08** » Thu Sep 15, 2011 7:36 am

Thanks Craig for your reply.
I am trying to delete records from target table on certain condition.

Step 1: Delete from table where Col1='ABC'
Step 2: Insert into Target_Table.

Please let me know if there are any workarounds other than writing a Stored Procedure

chulett · Post by **chulett** » Thu Sep 15, 2011 7:42 am

Two links to the ODBC stage, ordered properly, with the delete link being sent first. Constrain the delete link to only send the first row to the target, for example using @OUTROWNUM=1.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 15, 2011 7:45 am

Tip: Connectors are available in server jobs as well as in parallel jobs.

chulett · Post by **chulett** » Thu Sep 15, 2011 7:51 am

I thought Connectors were PX only... or at least initially. When did they come over to the Server side? However, I did see in the 8.5 documentation that basically every stage other than the Connectors are listed as 'deprecated'.

palak08 · Post by **palak08** » Thu Sep 15, 2011 10:16 am

Thanks Craig.. It worked.

The only problem is, I am loosing one record. If I use @OUTROWNUM=1 then that record is sent to the delete link and thus not get inserted into target table.

For Now- I am storing that one record in Seq file and inserting back into target.

Is there a better way to handle that one record as well?
Is there a way to change output execution order?

Thanks.

palak08 · Post by **palak08** » Thu Sep 15, 2011 10:17 am

Hey Ray,

I did not find ODBC connector in Datastage 8.1. Is this a new stage introduced in 8.5?

Thanks.

chulett · Post by **chulett** » Thu Sep 15, 2011 1:09 pm

You shouldn't be losing anything, that constraint should only be on the delete link and there shouldn't be any constraint on the normal loading link. Records don't have to go down one link or the other, they can go down any number of links.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 15, 2011 3:12 pm

Yes, availability began in 8.5.