AS400 / ODBC / User-defined sql

josy · Post by **josy** » Mon Mar 15, 2004 4:38 am

All,

I am trying to delete data on AS400 files using the ODBC plugin.
But it appears that when I run the job using User-defined SQL, it hangs and does not delete any data.
However, when I run the same job using the update action "Replace existing rows completely", it works properly, which means that the job does the "delete" then the "insert".
So, after many testing, it seems that whatever I run using User-defined SQL, the job hangs. It does it even for an update or an insert.

Any ideas?

PS : I am using datastage V5.2 (HP-UNIX) and ODBC 4.1

ogmios · Post by **ogmios** » Mon Mar 15, 2004 5:40 am

In the .odbc.ini file in the DataStage DSEngine directory activate Tracing: Trace/TraceFile and see what that gives.

Or have your DBA's give you a list of SQL statements DataStage tries to execute. To see whether what you actually execute is what you intended to be executed, you could be amazed.

Ogmios.

josy · Post by **josy** » Mon Mar 15, 2004 6:42 am

I have already changed my .odbc.ini file to activate the tracing.
There is nothing concerning the SQL statement in the trace file and actually the trace file does not help me at all!:-)

Regards

Josy

WoMaWil · Post by **WoMaWil** » Mon Mar 15, 2004 7:28 am

it is not easy to write the correct userdefined sql-code.

Please show us, what code you send via odbc and show us also how the genereated code for for example an update looks like.

Wolfgang

josy · Post by **josy** » Mon Mar 15, 2004 7:44 am

Here is an example of the SQL I am trying to execute :

DELETE FROM HMLIMSNRA.MILOCK1P WHERE (MLKEY = 2);

An update would be

UPDATE HMLIMSNRA.MILOCK1P set MLKEY=3 where MLLOCK=6;

Regards

Josy

peterbaun · Post by **peterbaun** » Mon Mar 15, 2004 8:20 am

Hi Josy,

I cannot remember if the following applies for ODBC on Unix, but on NT there is an ODBC parameter/option named

SQLConnectPromptMode

which you need to set to 1 - otherwise DataStage will hang if there is something wrong - eg. wrong password (long explanation - see prevous threads on this).
So my suggestion/question is - is there a similar parameter in the ODBC.ini file that can be set on unix - what will happen is that the actual errormessage will appear instead of the job hanging (on NT...).

Regards
Peter

josy · Post by **josy** » Mon Mar 15, 2004 8:37 am

I don't have any parameter like that on UNIX.

Thanks anyway

Josy

roy · Post by **roy** » Mon Mar 15, 2004 11:00 am

Hi,
AFAIK:
you can ask the AS400 guys to debug the process that handles the sql you send also ask them to check the log files (it's been a while so I don't remember the exact commands for debugging the process).
this should give you at least some idea on what is going on in the AS400 side, hopefully shading some light on your problem.

IHTH,

chulett · Post by **chulett** » Mon Mar 15, 2004 11:23 am

josy wrote:Here is an example of the SQL I am trying to execute :

DELETE FROM HMLIMSNRA.MILOCK1P WHERE (MLKEY = 2);

An update would be

UPDATE HMLIMSNRA.MILOCK1P set MLKEY=3 where MLLOCK=6;

What are "2", "3" and "6" above? Column numbers? The ODBC Stage uses question marks as parameter markers, not column numbers like OCI does. You'll need to code the statements more like:

Code: Select all

DELETE FROM HMLIMSNRA.MILOCK1P WHERE (MLKEY = ?);
UPDATE HMLIMSNRA.MILOCK1P set MLKEY=? where MLLOCK=?;

These are positional, by the way, so the first ? corresponds to the first Column defined, the second ? corresponds to the second Column, etc. I also believe it will get upset if you don't have the same number of columns and question marks in the SQL. Try having the Stage create 'normal' SQL and then switching it to User Defined, that will show you the syntax you are looking for.

josy · Post by **josy** » Mon Mar 15, 2004 11:37 am

I already used the question mark!
And I already tried having the Stage create 'normal' SQL and then switch it to User Defined.
But the funny thing is for the same statement:

- when I use the 'normal' SQL, it works
- when I switch to the User Defined sql, it hangs.

That is why I am a bit confused.
And I can't use the 'normal' SQL for a deletion only as it does not exist in the ODBC!!

chulett · Post by **chulett** » Mon Mar 15, 2004 11:51 am

josy wrote:And I can't use the 'normal' SQL for a deletion only as it does not exist in the ODBC!!

That's one of the (many) reasons I avoid ODBC if at all possible.

The only reason I asked about the question marks was the fact that they aren't in the SQL you posted and said you were using. Just for grins, can you post the exact User Defined SQL you are trying to run?

josy · Post by **josy** » Tue Mar 16, 2004 3:28 am

An example of user defined sql I am trying to run is :

DELETE FROM hmlimsnra.MILOCK1P WHERE (MLKEY = ?);

Regards

Josy

chulett · Post by **chulett** » Tue Mar 16, 2004 7:42 am

How many columns do you have defined in the link that is trying to run this SQL? One, I hope.

Try it without the trailing semi-colon for grins. The OCI stage doesn't like them, perhaps the ODBC stage is the same way.

roy · Post by **roy** » Tue Mar 16, 2004 11:45 am

Hi,
I was just wondering,
If you copy the exact generated sql and paste it as a new user defined SQL query does it work?
the generated sql should hold the syntax you need, so mimicing that syntax should work.
if not as I said ask the AS400 guys to debug it and see what was sent to the AS400 side this should help you get to a conclusion if this is a syntax issue or something else.

Good Luck,

josy · Post by **josy** » Wed Mar 17, 2004 11:26 am

Thanks everybody!
We have not yet solved our problem!!
But I think I am going to check with the AS400 team if they have any way to debug my process.

Regards

Josy

DSXchange

AS400 / ODBC / User-defined sql

AS400 / ODBC / User-defined sql

Re: AS400 / ODBC / User-defined sql