User Defined SQL Line Limit in ORAOCI9 stage.

[GavMagill]

Hi All

I would like to know if anyone else has had issues with the number of lines of SQL that can be entered into the User Defined SQL tab of the ORAOCI9 stage in Server jobs?

I have a Server job using an ORAOCI9 stage which uses a 40 line, formatted SQL statement in the Output User Defined SQL tab.

This query works fine when I run it manually using the View Data button but once compiled, the job fails during the run with the following message.
(Abnormal termination of stage SQLLinesTest..Sequential_File_84.IDENT1 detected)

If I unformat the SQL statement and just cram it in without any linefeeds or spaces to indent the SQL, the job compiles and runs ok.

Then if I slowly increase the number of lines by adding linefeeds, it eventually fails again when I get to 18 lines of SQL. Very odd.

I have had a look on the IBM support site and there is a bug that was fixed in the Version 8.1 Rollup Fixpack which limited the number of characters in a User Defined SQL statement to 65K but I am nowhere near that number in my query (approx 1.8K chars) and the version we are using (V8.1.2) includes this rollup fixpack anyway.

I intend to log a call with IBM but wanted to see if anyone else has seen similar behaviour in their Server jobs.

As I say I have a workround which is to just remove any formatting from the SQL statement but this looks ugly and unprofessional and I would like to find a solution to this problem.

Regards

[gateleys]

That's weird. We use 7.5.1, and have queries that are waaayyy longer than that, and we haven't had any issues... even with line feeds.

[GavMagill]

That's weird. We use 7.5.1, and have queries that are waaayyy longer than that, and we haven't had any issues... even with line feeds.

I also have Version 7.5.1 installed on my laptop and like you it has no problems with long SQL statements. This looks to be an issue with V8.x but I just wanted to confirm if anyone else had seen it before logging it with IBM.

[chulett]

viewtopic.php?t=130306