Parameter: Max Length
Moderators: chulett, rschirm, roy
Parameter: Max Length
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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I tried the search but I didnt find any results that could help me.ArndW wrote: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. ...
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?
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
It could be that you need to "escape" special characters before passing them to a job as parameters.
Example
needs to become
Example
Code: Select all
select col1, col2, col3 from a.table where datcol = '2008-02-18'
Code: Select all
select col1, col2, col3 from a.table where datcol = \'2008-02-18\'
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
Is there anything similar in the places where the parsing stops? As Stefan noted, it might be special characters.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Thanks for the reply frost.stefanfrost1 wrote:It could be that you need to "escape" special characters before passing them to a job as parameters.
Example
needs to becomeCode: Select all
select col1, col2, col3 from a.table where datcol = '2008-02-18'
Code: Select all
select col1, col2, col3 from a.table where datcol = \'2008-02-18\'
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.
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.ArndW wrote:Is there anything similar in the places where the parsing stops? As Stefan noted, it might be special characters. ...
The code of the shell script is basically:
cat <filename> | sed -e s/\'/\\\\\'/g
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.ArndW wrote:Is there anything similar in the places where the parsing stops? As Stefan noted, it might be special characters. ...
The code of the shell script is basically:
cat <filename> | sed -e s/\'/\\\\\'/g
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.Maveric wrote: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.
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 ?
Yes, you can use the ereplace() function to convert "'" to "\'", i.e.
Code: Select all
EREPLACE({string},"'","\'")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Thanks ArndW, you're a lifesaverArndW wrote:Yes, you can use the ereplace() function to convert "'" to "\'", i.e....Code: Select all
EREPLACE({string},"'","\'")
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