AS400 / ODBC / User-defined sql
Moderators: chulett, rschirm, roy
AS400 / ODBC / User-defined sql
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
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
Re: AS400 / ODBC / User-defined sql
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.
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.
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
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
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
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: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;
Code: Select all
DELETE FROM HMLIMSNRA.MILOCK1P WHERE (MLKEY = ?);
UPDATE HMLIMSNRA.MILOCK1P set MLKEY=? where MLLOCK=?;
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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!!
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!!
That's one of the (many) reasons I avoid ODBC if at all possible.josy wrote:And I can't use the 'normal' SQL for a deletion only as it does not exist in the ODBC!!
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org