Parameter: Max Length

[Havoc]

Hi,

I have a sequence design which uses an Execute Command Activity stage which 'cat's an SQL file and passes the CommandOutput as a value to a parameter in a job.

The problem is that the whole SQL is not being passed to the Parameter. The total number of characters in the SQL file is 1169. I have basically two questions regarding this problem:

1) What is the maximum string length that a parameter can hold?
2) Is there another alternate solution to the above problem?

Parameter is being used in a DB2 Enterprise stage.

Thanks in advance

[ArndW]

The 1169 looks very much like the limit from your command line, not from DataStage. Try using Google to search for "{your UNIX} maximum command line" to get additional information.

[Havoc]

The 1169 looks very much like the limit from your command line, not from DataStage. Try using Google to search for "{your UNIX} maximum command line" to get additional information. ...

I tried the search but I didnt find any results that could help me.
The funny part is, i am passing two SQLs as two separate parameters and each of them hit different max limits:

The first SQL stops at the 1138th character
while
the second SQL stops at the 612nd character

Any idea as to why this could be happening and any remedies?

[stefanfrost1]

It could be that you need to "escape" special characters before passing them to a job as parameters.

Example

Code: Select all

select col1, col2, col3 from a.table where datcol = '2008-02-18'

needs to become

Code: Select all

select col1, col2, col3 from a.table where datcol = \'2008-02-18\'

[ArndW]

Is there anything similar in the places where the parsing stops? As Stefan noted, it might be special characters.

[Havoc]

It could be that you need to "escape" special characters before passing them to a job as parameters.

Example

Code: Select all

select col1, col2, col3 from a.table where datcol = '2008-02-18'

needs to become

Code: Select all

select col1, col2, col3 from a.table where datcol = \'2008-02-18\'

Thanks for the reply frost.
Yes, that has been taken care of. I do that while reading the SQL using a sed command to replace ' with \'.

When i check the value of the Parameter in the Director Log, its been cut short.

[Havoc]

Is there anything similar in the places where the parsing stops? As Stefan noted, it might be special characters. ...

ArndW as you suggested, I ran the shell script that parses the file and the output from the shell script at the Unix command prompt is cut short and not at DataStage level.

The code of the shell script is basically:

cat <filename> | sed -e s/\'/\\\\\'/g

[Havoc]

Is there anything similar in the places where the parsing stops? As Stefan noted, it might be special characters. ...

ArndW as you suggested, I ran the shell script that parses the file and the output from the shell script at the Unix command prompt is cut short and not at DataStage level.

The code of the shell script is basically:

cat <filename> | sed -e s/\'/\\\\\'/g

[Maveric]

I think you will need to escape even the = character. I had a problem with that once. it is considered as a delimiter or something and it would stop at the = character.

[Havoc]

I think you will need to escape even the = character. I had a problem with that once. it is considered as a delimiter or something and it would stop at the = character.

Thanks for the reply Maveric, but looks like the = character needn't be quoted. Earlier SQLs with = have been executed successfully. It has something to do with the way the command output is being returned. Even when running the script in AIX at command line , the output is being cut short.

Alright, further investigation reveals that:

when using 'sed' the output is being cut short by 2 lines but when doing a normal 'cat' the whole file is being read.
Is there a way i can escape the quote (i.e. convert occurences of ' to \' in DS by using the Convert () function ?

[ArndW]

Yes, you can use the ereplace() function to convert "'" to "\'", i.e.

Code: Select all

EREPLACE({string},"'","\'")

[Havoc]

Yes, you can use the ereplace() function to convert "'" to "\'", i.e.

Code: Select all

EREPLACE({string},"'","\'")

...

Thanks ArndW, you're a lifesaver

Solution: Removed the sed which was piped along with the cat and used the CommandOutput from the Execute Command and used EREPLACE function.

Thanks to all for their valuable suggestions