SQL in Routines

[cooperjv]

Could you please let me know if there is any documentation on the proper use of SQL in routines.

[kduke]

In the BASIC.pdf it talks about it. Are you talking about SQL against hash files or Universe files or other databases. Most of the time this should be done in jobs. You lose the GUI viewing capabilities when you run SQL in routines. It sort of defeats the purpose of DataStage.

Maybe you could give us more detail on why you need to do this in a routine and not in job. That may help us to be able to see your problem better and maybe understand your solution better too.

[cooperjv]

In the BASIC.pdf it talks about it. Are you talking about SQL against hash files or Universe files or other databases. Most of the time this should be done in jobs. You lose the GUI viewing capabilities when you run SQL in routines. It sort of defeats the purpose of DataStage.

Maybe you could give us more detail on why you need to do this in a routine and not in job. That may help us to be able to see your problem better and maybe understand your solution better too.

I have to split data from a TEXT field in SQL Server into strings of 72 chars breaking the string at a "Space" or "Enter". I am doing this in a Routine by passing in the TEXT filed.

Could you please let me know where the BASIC.pdf is located

[ray.wurlod]

There is a library of functions collectively called the BASIC SQL Client Interface that mimics the ODBC 2.0 API. If you use this you would not (probably) be limited to 72 characters per line, but would need to have an ODBC data source name configured.

Why can't you use DataStage components to perform your tasks? You've paid lots of money for the tool, now you want to bypass the tool and do it yourself?

Other possibilities include having the routine invoke a BAT file that executes the SQL and to capture the output. Read about the DSExecute subroutine as a method for accomplishing this.

All the DataStage manuals are installed with your client software, unless you did a custom install and chose to omit manuals. Default location is C:\Program Files\Ascential\DataStage\Docs.

[ray.wurlod]

I have to split data from a TEXT field in SQL Server into strings of 72 chars breaking the string at a "Space" or "Enter". I am doing this in a Routine by passing in the TEXT filed.

OK, you don't need SQL in the routine for this.

Code: Select all

FUNCTION Split72(TheText, FileName)

SplitText = Fmt(TheText, "72T")  ; * breaks on white space, max length 72 characters

* Now, what do you want to do with these lines?
* How do you want them returned?  Here we'll write them into a sequential file.

Ans = 0 
Shell = (If System(91) Then "DOS" Else "UNIX")

Loop
   Remove Line From SplitText Setting MoreLines
   Command = "echo " : Line : " >> " : FileName
   Call DSExecute(Shell, Command, Output, ExitStatus)
   Ans += ExitStatus
While MoreLines
Repeat

RETURN(Ans)

It is quite inefficient to call DSExecute repeatedly like this; the preferred solution would use BASIC statements to open, write to, and close the sequential file directly (OpenSeq, WriteSeq, CloseSeq and others), but I thought that the above would be clearer.

[cooperjv]

I have to split data from a TEXT field in SQL Server into strings of 72 chars breaking the string at a "Space" or "Enter". I am doing this in a Routine by passing in the TEXT filed.

OK, you don't need SQL in the routine for this.

Code: Select all

FUNCTION Split72(TheText, FileName)

SplitText = Fmt(TheText, "72T")  ; * breaks on white space, max length 72 characters

* Now, what do you want to do with these lines?
* How do you want them returned?  Here we'll write them into a sequential file.

Ans = 0 
Shell = (If System(91) Then "DOS" Else "UNIX")

Loop
   Remove Line From SplitText Setting MoreLines
   Command = "echo " : Line : " >> " : FileName
   Call DSExecute(Shell, Command, Output, ExitStatus)
   Ans += ExitStatus
While MoreLines
Repeat

RETURN(Ans)

It is quite inefficient to call DSExecute repeatedly like this; the preferred solution would use BASIC statements to open, write to, and close the sequential file directly (OpenSeq, WriteSeq, CloseSeq and others), but I thought that the above would be clearer.

Thanks for the help
The requirement is to read the SQL Server Table which has the TEXT Field and then break it up into 72 char lines and then write these 72 char lines to another table. That is why I need SQL in the routine to write to the table. Please let me know if there any other way this can be done.

[xcb]

Following Rays advice once you have landed your data into a sequential file, create another job that reads this file and then populates the target table. The job is quite simple and would look something like this.

Code: Select all

Seq -> Transform -> ODBC

You might use the MSOLE? stage (I can't remember exactly what its called) instead of the ODBC stage if you are loading into SQL Server.

If I was you I would avoid having to write a routine that makes BCI calls, especially when you can achieve the same results with Datastage components.

[kduke]

It is a lot easier to read and write to hash files or sequential files in BASIC. Stick to those. Write a job to extract the data to a hash file. You could easily process this in BASIC use Ray's routine.