I was under the impresion that I could take a SQL statment, like an Update statment and put that into a Routine in Data Stage and use it in a transformation. When I try to complile the routine i get the error:Compiling:
Source = 'DSU_BP/DSU.JOCImportTradeDefines', Object = 'DSU_BP.O/DSU.JOCImportTradeDefines'
0002 Update STG_JOC_IMP_TBL
^
Variable Name (UNDEFINED) unexpected, Was expecting: Assignment Operator
1 Errors detected, No Object Code Produced.
Im thinking that i was misled my my course instructor as to where I need to aply this SQL. Any thoughts?
Jim
Hmmmm...SQL Logic?
Moderators: chulett, rschirm, roy
Jim
The syntax in a routine is not SQL but DataStage BASIC which was derived from Universe BASIC. Universe is a database sold by IBM.
You can shell out and execute SQL. You can execute SQL through ODBC in a routine. Both are not simple to program. The easiest way is to do it in a stage. When you setup a source stage or a lookup stage then the SQL can be generated from the column names and the table names or be user defined. I would set it up generated first and then switch to user defined. User defined is selected by a tab in older versions and maybe different based on if it is a plugin or not but look for it or ask for help. DataStage help can walk you through it.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
The syntax in a routine is not SQL but DataStage BASIC which was derived from Universe BASIC. Universe is a database sold by IBM.
You can shell out and execute SQL. You can execute SQL through ODBC in a routine. Both are not simple to program. The easiest way is to do it in a stage. When you setup a source stage or a lookup stage then the SQL can be generated from the column names and the table names or be user defined. I would set it up generated first and then switch to user defined. User defined is selected by a tab in older versions and maybe different based on if it is a plugin or not but look for it or ask for help. DataStage help can walk you through it.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your instructor was correct in saying that you can execute SQL in a routine. However, that is not the entire story, as Kim suggests.
You can execute SQL against DataStage tables (including hashed files) via a call to DSExecute. You can execute SQL through an ODBC driver via the BCI functions. You can not execute "programmatic SQL" directly, because of the nature of the DataStage Engine, which is "database-agnostic".
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
You can execute SQL against DataStage tables (including hashed files) via a call to DSExecute. You can execute SQL through an ODBC driver via the BCI functions. You can not execute "programmatic SQL" directly, because of the nature of the DataStage Engine, which is "database-agnostic".
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518