DataStage Scribble Sheet

Various
Allow DataStage to see a UniData account
Need to activate Uniserver on the UniData account (free licence). This allows Objectcall to work from
DataStage.
Useful hex numbers

Character CHAR() Hex

Tab 09 09
Line Feed 10 0A
Carriage Return 13 0D
Escape 27
Space 32
Oracle null written to hash file 128
Text mark 251
Sub-multi-value mark 252
Multi-value mark 253
Attribute mark 254
Oracle null See below
Pick null – i.e. “” 0

An Oracle null is defined by a bit being set, and as such can not be tested for directly from DataStage.
However, you can use an NVL to set a null value in the Oracle stage to something else, and then use
that same value in the lookup derivation:

ORAOCI stage, user-defined SQL:

NVL(SOURCE_SYSTEM_K4_VALUE,-999)=:4

Key field derivation in the look up stage:

IF lu.ANIMAL_PRODUCTION_PK = "" THEN -999 ELSE lu.ANIMAL_PRODUCTION_PK

ODBC’s
Server details

From DataStage Manager, to enable a new ODBC driver need to update the following DataStage
config files: .odbc.ini uvodbc.config
Note that there can also exist a local uvodbc.config file for a project. If there isn’t DataStage uses the
default file.

This default file is located in $DSHOME / DataStage / DSEngine

Or possibly /dsadm/DataStage//DSEngine/

To get to $DSHOME type: cd `cat /.dshome`

The file is called .odbc.ini

Stop/Start DataStage script

su to root
cd to ./DSEngine/bin
execute ./uv –admin –stop
execute ./uv –admin –start to start

Look in: /$DSHOME/sample/ds.rc

Command to find number of files that can be opened: ulimit –aH

09/12/21 Page 1 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

To increase number of files that can be opened: ulimit –n 512

Place this after the umask command

DataStage environment variables on Unix

Look in: .. /DSEngine/dsenv

DataStage and Oracle etc. variables set in here.

Can also set Unix variables and then use them in DataStage jobs (dsenv is read before every DataStage
job. Accessed through Job Control => Parameters => Add Environmet Variable (which also allows
creation of new environment variables)

UniVerse on NT useful bits

CREATE.FILE {filename} {type} {modulo} {separation}

File types 1 and 19 are not hashed, good for programs. Type 30 is Dynamic. Types 2 to 18 are hashed.
Dictionary level defaults to type 3, modulo 1, separation 2.

To create a type 2 file, mod and sep of 1, dictionary uses defaults:

CREATE.FILE {filename} 2 1 1

To create a dynamic file, which is type 30, a directory (folder) with a flat file for the dictionary:
CREATE.FILE {filename} DYNAMIC
or
CREATE.FILE {filename} 30 1 2
or
CREATE.FILE {filename} 19

At DOS, both type 19 and type 30 (directory) files appear as a <DIR>, the difference being that the
type 19 (and type 1) directory is empty, whereas the type 30 directory contains files called DATA.30
and OVER.30

Type 19 files is a normal directory

So use type 19 files to ftp programs back and forth.

To create a file in a different directory or path name, use DataStage or refer to JCFILE CREATE.FL

Distributive Files on UniVerse (Distributed? Unidata?)

DF.DEFINE command
DF.MODIFY command
Usage: DF.MODIFY -d dist_file -o op -p part_file -n _part_number

UniVerse on UNIX useful bits

As above, plus:
Running UNIX commands from within UniVerse paragraph or program:
sh –c “UNIX command”

Inverting the case in a UniVerse session

At TCL type: PTERM -CASE NOINVERT
Then to revert: PTERM -CASE INVERT

09/12/21 Page 2 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Look for large records within a hash file

Copy RANGE2, RANGE.COUNT and CD.VOC from JCFILE into VOC.
Then run these commands to show how many items in the file are greater than 1000, 2000, 3000 etc.
bytes:
CD CD.VOC RANGE2 RANGE.COUNT
LIST {File name} BY RANGE2 BREAK.ON RANGE2 TOTAL RANGE.COUNT DET.SUP

Output from RETRIEVE command to UNIX or DOS file

Use the COMO command to spool screen output to a file in the {Project}/&COMO&/ directory

UniData on UNIX useful bits

This shows how to prompt for variables from within a paragraph (<<Variable>>), use of the DATA
command to feed data into a prompt, and also how to output the result of an English statement to a file
at UNIX level:
001: PA
003: DISPLAY If you really want to run this please type YES
004: IF <<CONFIRM>> NE 'YES' THEN GO FINISH
006: COMO APPEND CLEAR.HVDC.FILES
007: DATE
008: ! who am I
003: AE IACS.ST<<I2,Scheme Year>> <<I3,Iacs No>>
004: DATA 101
005: DATA R 343963
008: DATA FI
010: SELECT IACS.ST<<I2,Scheme Year>> "<<I3,Iacs No>>" BY.EXP IA.CORE
SAVING IA.CORE
011: COPY FROM CORE.SYSTEST TO CORE OVERWRITING
012: DATA Y
003: LIST IACS.ST_HV2005 SP.RECDT COL.HDG "SP.RECDT" TO DELIM ","
<<Enter filename for output>>
019: COMO OFF
021: FINISH: *

CREATE.FILE DIR filename creates UNIX directory

CREATE.FILE filename DYNAMIC creates a dynamic hashed file

Universe debug tool – RAID

/ [string] Searches the source for [string]
[line] B Sets break point at line [line]
B* Lists break points
C Continue program execution
D Delete break point at current line
D* Deletes all break points
[line]G Continue program execution from [line]
L Display next line to be executed
variable M Continue execution until variable changes
variable=VALUE M Continue execution until variable changes to VALUE
Q Quit raid
[n] S Steps through the code one, or n, lines at a time
[n] S* Steps around subroutine calls, i.e. subroutine becomes a single line
T Display call stack trace
[line] W Displays 10 lines of source code around [line]
[line] Z Displays the next 10 lines of source code

09/12/21 Page 3 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

[n] - Subtracts n from the current line

variable / Displays the value of variable
variable ! STRING Changes the value of variable to STRING

DataStage on NT useful bits

Job Control priority
Any code in the Job Control tab is executed before any stages in the diagram window are executed

DataStage Admin menu

At ECL type DS.TOOLS for main tools menu, or DS.PLADMIN for process and locks menu

Starting a DataStage job without an input file

Simply put a stage variable in the first Transform, for instance called ‘DummyCounter’, with ‘0’ as the
starting value. Then increment it in the stage variable box “DummyCounter + 1” and then in all the
constraints put “DummyCounter < 2”. This will run for 1 row. More than 2 runs for more rows.

DataStage shortcut
<CTRL> E to edit the highlighted stage derivation

DataStage system files

C:\Ascential\DataStage\ServerEngine\UVTemp – this directory holds temporary UniVerse files,
mainly sort and capture files. Capture files seem to hold ECL output. This pathname is configurable,
certainly in Unix anyway.

Example of the use of Stage Variables

Using COMMON instead of stage variables

Another way to keep things in memory is by using a common area. This example increments a counter
while NextId remains the same (GeneralCounter routine):

COMMON /COUNTER/ OLDPARAM, TOTCOUNT

NEXTID = Arg1

IF UNASSIGNED(OLDPARAM) THEN
OLDPARAM = NEXTID
TOTCOUNT = 0
END

IF NEXTID = OLDPARAM THEN

TOTCOUNT +=1

09/12/21 Page 4 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

END ELSE
OLDPARAM = NEXTID
TOTCOUNT = 1
END

Ans = TOTCOUNT

DataStage Designer: Folder stage versus Sequential File stage

 Folder stage is much quicker
 All rows read in from the source folders end with CHAR(254) (not C/R L/F)
 If these are converted to CHAR(253) and written to a hash file, that hash file can then be
normalised

Put messages into the log

EQUATE RoutineName to “…whatever…”
DSLogInfo(MSG,RoutineName)
DSLogWarn(MSG,RoutineName)
DSLogFatal(MSG,RoutineName)

Useful DataStage files

 DS_ROUTINES {routine name} attribute 15 contains the code used in the routine. See JCFILE
JC_RTNS for routine to weed out the code and write it into a program
 DS_JOBS {jobname} attribute 5 contains the job number
 DS_JOB{job number}- records contain the code used in the stages
 DS_JOBOBJECTS J\{job number}\ transform id - records contain the code used in the stages –
this is for DataStage 5.1
 DS_JOB44 V0S12P2 (or in 5.1 DS_JOBOBJECTS J\44\V0S81P2) for example,
 <2> called OLETYPE = CTrxOutput
 <3> called NAME = {link name}
 <6> = Constraint
 <10> = Constraint
 <14> = Output derivation column name
 <16> = Data element
 <17> = Type code, where
 1 = Char
 2 = Numeric
 3 = Decimal
 4 = Integer
 5 = SmallInt
 9 = Date
 10 = Time
 11 = Timestamp
 12 = VarChar
 -1 = LongVarChar
 -2 = Binary
 -4 = LongVarBinary
 -5 = BigInt
 -6 = TinyInt
 -7 = Bit
 -9 = NVarChar
 <18> = Length
 <19> = Scale
 <20> = Nullable
 <21> = Key
 <22> = Display length

09/12/21 Page 5 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

 <23> = actual derivation code

 <25> = copy of <23> with any transforms applied
 <26> = something to do with input (output?) columns
 <45> = Constraint?
 <100> = Constraint?
 J\nn\ROOT item in file DS_JOBOBJECTS
 <2> called OLETYPE = CJobDefn
 <3> called NAME = {job name}
 <4> = Short job description
 <7> = Full job description
 <9> = Before job subroutine and argument list
 <11> = Job control code, if any, CR/LF delimited
 <13> = {text}/number of parameters
 <14> = parameter name, multi-valued
 <15> = ??, multi-valued
 <16> = prompt, multi-valued
 <17> = default value, multi-valued
 <18> = help text, multi-valued
 <19> = type code, multi-valued
 <31> = List of other jobs called – not used?
 <94> to <96> = Job parameter details
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CTransformerStage
 <3> called NAME = {transform name}
 <37> = CStageVar/{n}
 <38> = Stage variable names, multi-valued
 <39> = Stage variable description fields, multi-valued
 <40> = Stage variable derivations, multi-valued
 <41> = Stage variable initial values, multi-valued
 <43> = Expanded copy of <40>, rebuilt when transform OK’d in Designer
 <45> = Stage variable?
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CCustomStage
 <3> called NAME = {stage name}
 <7> = Id of CCustomeOutput output link
 <8> = stage type, eg ORAOCI8
 <17> = parameters (with #’s), multi-valued
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CCustomOutput
 <3> called NAME = {output link name}
 <14> = multi-valued details of passive stage: table name, options, SQL code
 <16> = input column names
 <23> = key
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CCustomInput
 <3> called NAME = {input link name}
 <15> = multi-valued details of passive stage: table name, options, SQL code, create/drop table
 <16> = input column names
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CHashedFileStage
 <3> called NAME = {hash stage name}
 <8> = directory path
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CHashedInput
 <3> called NAME = {input link name}
 <6> = Hash file name

09/12/21 Page 6 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

 <7> = Clear file option (1 or 0)

 <8> = Backup file option (1 or 0)
 <16> = Allow stage write cache option (1 or 0)
 <17> = Create file options, if blank file not created
 <18> = Delete file option (1 or 0)
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CHashedOutput
 <3> called NAME = {link name}
 <6> = Hash file name
 <7> = Select criteria (input or look-up file to this job)
 <9> = {Normalised field name} or the text Un-Normalised
 J\nn\V0S… item in file DS_JOBOBJECTS for Sequencer
 <2> called OLETYPE = CJSJobActivity
 <3> called NAME = {stage name}
 <11> = Execution action: 0 = Run; 1 = Reset if required then run; 2 = Validate; 3 = Reset
 <12> = Called Job name
 <13> to <18> - internal parameters for job running
 <15> = invocation id expression
 <16> = 4 if invoking multiple instance job
 <17> = <15>
 <19> to <24> - parameters
 <20> = parameter name in job being called
 <21> = actual value fed through, calling job parameter name or “hard coded value”
 <23> = <21> ?
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CJSSequencer
 <3> called NAME = {blue sequencer name}
 <8> = output mode: 0 for ALL; 1 for ANY
 J\nn\V0S… item in file DS_JOBOBJECTS
 <2> called OLETYPE = CJSActivityOutput
 <3> called NAME = {output link name}
 <6> Trigger expression Type: 1 Otherwise; 2 Executed OK; 3 Failed; 4 Warnings; 5 User
Status; 6 Conditional
 <8> Trigger expression Text: N/A; “Executed OK” in quotes; “Executed Failed” ; “Executed
finished with warnings”; = {user status}; <LHS> = <RHS>
 DS_METADATA
 <4> = Short description
 <5> = Long description
 <9> = ‘0’ for normal file, ‘1’ for associated (mv’d) file
 <11> = text / number of fields
 <12> = Column name
 <13> = Description with CR/LF’s embedded
 <14> = Data element
 <15> = SQL type code, defined as above
 <16> = Length
 <17> = Scale
 <18> = Nullable – 0 or 1
 <19> = Key – 1 or 0
 <20> = Display length
 <21> = Association Only displayed if <9> = 1
 <22> = Position (attribute number) Only displayed if <9> = 1
 <23> = Attribute type – ‘S’ingle or ‘M’ulti-valued Only displayed if <9> = 1
 <98> = text / number of fields
 <102> = Dunno, set to multi-valued zeros v7.5: Level?
 <103> = text: "DISPLAY_NUMERIC" v7.5
 <104> = set to multi-valued zeros
 <108> = ditto

09/12/21 Page 7 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

 <110> = ditto
 <111> = text: "DISPLAY" v7.5
 <113> = text: "zoned, default=0" v7.5
 <114> = set to multi-valued zeros
 <115> = ditto
 <116> = ditto
 DS_CONTAINERS
 Similar to DS_JOBS but for containers
 <5> = JOBNO, recs exist in DS_JOBOBJECTS as above
 RT_STATUSnnnn where nnnn = JOBNO
 Job status file, id is JobName.Instance and JobName.Instance.1
 In the .1 record can change status – attribute <2> - that appears in Director (for instance if can
not clear status file normally)
 3 – Aborted
 2 – Finished
 0 or 1 – Running?

See JCFILE JC_TRANS for routine to duplicate derivations and many other useful bits. Also see
JC_TRANSALL, JC_TABLES and JC_METADATA

Calling DataStage routines from ECL or within another routine

For instance from within a DataStage job the AfterJob routine would be ExecDOS and the parameter
passed would be whatever.
From ECL or within another routine need to do:

CALL DSU.ExecDOS(TEXT,ERR) with both the DSU. at the front and the ERR argument – good
and useful example of this is JCFILE CREATE.FL to create files in a remote path name

Calling DataStage functions from within a routine

* Include this in program to correctly use datastage functions

$INCLUDE DSINCLUDE JOBCONTROL.H

These are included in the routine used to set / change a job’s parameters:
$INCLUDE DSINCLUDE DSD_STAGE.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE DSINCLUDE DSD.H
$INCLUDE DSINCLUDE DSD_RTSTATUS.H

See routine SetSequencerParameters currently in project DevERDP in category

IACS_update_for_current_systems which picks up a date from VOC and feeds it into a parameter

Calling Transform functions from within another routine

Either use the CALL.DSU… method above, or define the function at the top of the routine and then use
it in the same way as in a derivation to feed into a variable:

Deffun FindMatchingContracts(InFile, OutFile, DBSOURCE) Calling "DSU.FindMatchingContracts"

VAR = FindMatchingContracts(InFile,OutFile,DBSOURCE)

At top of routine:
Deffun FindMatchingContracts(InFile, OutFile, DBSOURCE) Calling "DSU.FindMatchingContracts"

Later in code:
VAR = FindMatchingContracts(InFile,OutFile,DBSOURCE)

09/12/21 Page 8 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Calling a DataStage Job from within a routine

Can run DataStage jobs from command line (UNIX prompt) by using dsjob. Can also run a job
remotely using this command, on another DataStage server for instance. Here’s example Brian had to
run jobs on two different servers:
You can run dsjob from Command Line stage remotely and pass parameters as well
(undocumented feature) in the following format:

dsjob -run #MyParam# (I set this to -wait) DevScanDocsBuildTxtForBatch - this works locally,
but dsjob also allows a login to a remote server - dsjob -server servername -user username
-password password

Refer to DataStage Server Job Developer’s Guide manual for dsjob documentation.

Example to run a job locally then move a file:

dsjob -run
-param PATH=/radf/hfa/global/DataStage/Projects/IACS_SYSTEST/FlatFiles
-param FNAME=MetricsBrm9 -wait -local IACS_SYSTEST ProduceMetricsFromLogs
#
mv ./FlatFiles/MetricsBrm9.csv ../../HVDC/HVDC_XML

The –wait waits for the job to finish. The –local is only really necessary if changing environment
variables. Other dsjob options are:

dsjob [-server servname] [-user userid] [-password pwd]

–run
[-mode] [-param p1 –param p2 etc.] [-wait]
ProjectName JobName

Refer to JCFILE runMetrics for an example of this

Calling a DataStage Job from within a routine

Refer to JCFILE Call_DSJob_from_routine which shows the code that needs to go into the routine to
do the calling

Or just call the job from within Job Control

Run a DataStage export from DOS in a batch job (can be scheduled)

: Set Variables

set BKDBASE=PRBK

set DSPROJECT=ProdProbis

set DAYNUM=%date:~4,2%

dscmdexport /H=erdpds1 /U=DataStage /P=datateam %DSPROJECT%

E:\DSProjectBKP\DMSPresentation\%DAYNUM%_%DSPROJECT%.dsx /V >
E:\DSProjectBKP\DMSPresentation\%DAYNUM%_%DSPROJECT%.txt

Executing an ECL command from within a batch job

Use the following type of command in the batch job:
Call DSExecute("UV", "CLEAR.FILE ORDER_CUSTOMER", Output, RC)

Executing a TCL command from within a batch job

Call DSExecute("TCL", InputArg, Output, SystemReturnCode)

09/12/21 Page 9 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Alternatively call the DataStage routine ExecTCL which uses DSExecute

Executing a DOS command from within a batch job

Call DSExecute("DOS", InputArg, Output, SystemReturnCode)
Alternatively call the DataStage routine ExecDOS which uses DSExecute

Executing a Unix shell script from within a batch job

Call DSExecute("UNIX", command, Output, SystemReturnCode)
Alternatively call the DataStage routine ExecSH which uses DSExecute
Call DSU.ExecSH(command,SystemReturnCode)

For example (on Unix), write a script and run it using ftp to move files around:

OpenPath "." To Fv Else

TXT<-1> = "Unable to write file to ."
End

* Check here for any errors so far

IF TXT <> "" THEN

TXT = CHANGE(TXT,@AM,"; ")
Call DSLogFatal(TXT,RoutineName)
END

* Now write the script to execute – note the “quote site…” bit to do a remote chmod on the file after putting it

Script = "open worcs"

Script<-1> = "user f803163 phant0m"
Script<-1> = "lcd /radf/sinay/uat/temp"
Script<-1> = "cd ../erdp"
Script<-1> = "mdelete ":WILDCARD
Script<-1> = "put ":FILENAME:VERSION
or Script<-1> = "mput ":WILDCARD
Script<-1> = "quote site chmod 646 ":FILENAME:VERSION
Script<-1> = "bye"

ScriptName = "IUC090script"
Write Script On Fv, ScriptName Else
Call DSLogFatal("Unable to write file ":ScriptName:" to .",RoutineName)
End

command = "ftp -i -n < ":ScriptName

Call DSU.ExecSH(command, SystemReturnCode)

Delete Fv, ScriptName On Error Null Else Null

DataStage Macros
The following can be used within the derivations of a transform to return the information:

Return the name of this job DSGetJobInfo(DSJ.ME,DSJ.JOBNAME)

Sets the DataStage internal SetUserStatus(inputlinkname.ROW_COUNT)
variable UserStatus, for instance
with the output from an Oracle
COUNT command
Feed through the contents of From a sequencer: click on Insert parameter value
UserStatus as a parameter Find the jobname where user status was set
Click on $UserStatus

09/12/21 Page 10 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Returns the number of rows DSGetLinkInfo(DSJ.ME,"TransformName","LinkName",

processed in a link DSJ.LINKROWCOUNT)
Returns the number of rows Call a DataStage routine which does the following:
processed in a link in another job JOBHANDLE = DSAttachJob(JobName,DSJ.ERRWARN)
Ans = DSGetLinkInfo(JOBHANDLE,TransformName,LinkName,DSJ.LINKROWCOUNT)

COMMON command to include in DataStage routines:

$INCLUDE DSINCLUDE JOBCONTROL.H

DataStage warning statii

These can be found in the directory \Ascential\DataStage\Projects\{project name}\dsinclude in a file
called JOBCONTROL.H which shows return values for DSJ.JOBSTATUS etc. For example:

Equ DSJS.RUNNING To 0 ;* This is the only status that means the job is
actually running
Equ DSJS.RUNOK To 1 ;* Job finished a normal run with no warnings
Equ DSJS.RUNWARN To 2 ;* Job finished a normal run with warnings
Equ DSJS.RUNFAILED To 3 ;* Job finished a normal run with a fatal error
Equ DSJS.VALOK To 11 ;* Job finished a validation run with no warnings
Equ DSJS.VALWARN To 12 ;* Job finished a validation run with warnings
Equ DSJS.VALFAILED To 13 ;* Job failed a validation run
Equ DSJS.RESET To 21 ;* Job finished a reset run
Equ DSJS.CRASHED To 96 ;* Job has crashed
Equ DSJS.STOPPED To 97 ;* Job was stopped by operator intervention
(can't tell run type)
Equ DSJS.NOTRUNNABLE To 98 ;* Job has not been compiled
Equ DSJS.NOTRUNNING To 99 ;* Any other status

Accessing job statii from within Job Control

HJOB = DSAttachJob(ID, DSJ.ERRNONE)
STATUS = DSGetJobInfo(HJOB, DSJ.JOBSTATUS)
JOBNAME = DSGetJobInfo(HJOB, DSJ.JOBNAME)

IF JOBNAME = -1 THEN
* Ignore jobs that are not compiled
END ELSE
BEGIN CASE
CASE STATUS = DSJS.RUNFAILED
TXT = "Job ":JOBNAME:" status Run Failed (Aborted)"
CASE STATUS = DSJS.VALFAILED
TXT = "Job ":JOBNAME:" status Validation Failed"
CASE STATUS = DSJS.CRASHED
TXT = "Job ":JOBNAME:" status Crashed"
CASE STATUS = DSJS.STOPPED
TXT = "Job ":JOBNAME:" status Stopped"
CASE STATUS = DSJS.NOTRUNNABLE
TXT = "Job ":JOBNAME:" status Not Runnable"
CASE 1
TXT = "Job ":JOBNAME:" okay - ":STATUS
* Call DSLogInfo(TXT,RoutineName)
TXT = ""
END CASE

IF TXT <> "" THEN

Call DSLogWarn(TXT,RoutineName)
END

Ensure job is in a runnable state from within Job Control (Batch job)
* Ensure job is in a runnable state

09/12/21 Page 11 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

NOTRUNNABLE = 98
RUNFAILED (Aborted) = 3
CRASHED = 96
Finished = 1
Compiled = 99
Reset = 21

* Ensure job is in a runnable state

Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
IF Status = DSJS.RUNFAILED OR Status = DSJS.CRASHED THEN
ErrCode = DSRunJob(hJob1, DSJ.RUNRESET)
ErrCode = DSWaitForJob(hJob1)
hJob1 = DSAttachJob("JOBNAME", DSJ.ERRFATAL)
If NOT(hJob1) Then
Call DSLogFatal("Job Attach Failed:JOBNAME", "JobControl")
Abort
End
END

DataStage calling an Oracle Stored Procedure

Needs to have an ORAOCI stage which does something (eg. User defined SQL to do “SELECT
dummy FROM dual”. In the Before [SQL] tab put the following:

CALL {PROCEDURE NAME}(#Parameter#)

CALL EXTRACT_ANIMAL_DATA(#LOAD_BATCH_ID#)

Even if there is no parameter must have the brackets at the end:

CALL PR_TEMP_FIX_FROM_TO_DATES()

Merging data from sequential files

 There is a merge transform within DataStage which allows 2 sequential files to be merged and
output fields from both files.
 Can also use an ORAOCI file stage with a user-defined SQL statement that joins the relevant files
(can have more than 2) and outputs the necessary fields.

Accessing local machine from a UniVerse stage

In the box called Data source name put: localuv

Creating hash files in another path

Creating File DUPLICATES, Executing Command =
C:\Ascential\DataStage\ServerEngine\bin\mkdbfile d:\FTPData\MigrationDev\DUPLICATES 30
1 4 20 50 80 1628
See JCFILE CREATE.FL

Incorporating field from another file in the file/table stage

For example, have an ORAOCI stage for one table, but want to also import a related field from another
table, where the 2 tables have a common field (like a key field):
 In the ORAOCI stage, OUTPUT tab => GENERAL => Table names field, put the name of both
tables separated by a comma, so STH_Customers becomes STH_Customers,STH_Bookings
 In => COLUMNS load the relevant fields from both files
 In => GENERAL => Query Type change to User-defined SQL query
 In => SQL => User-defined change the query to include the bold bits below:
 SELECT Account_Number,Lessee_Name,…etc… FROM STH_Customers
 SELECT C.Account_Number,C.Lessee_Name,…etc…,B.Insurance_Indicator FROM
STH_Customers C,STH_Bookings B WHERE C.Account_Number = B.Account_Number

09/12/21 Page 12 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

 Note the addition of the second file name, B and C in lieu of the full file names, and the where
clause

Using an ODBC stage to access, select, look-up on a txt file (on NT)
This allows sql statements on a sequential (.csv or .txt) file!
 Save the csv file as a tab delimited text file
 Use Control Panel to set up an ODBC driver on the (NT) server:
o System DSN
o Microsoft text driver
o Select the directory
o .TAB
o define format
o tick Column Name Header
o select OEM box
o click on GUESS box (if change name click Modify)
o doing this will generate a Schema.ini file
o If ever need to modify anything delete this Schema.ini file first
 DataStage Manager:
o Import table definitions from the driver just created
 DataStage Job
o Use ODBC stage
o Quote = 000
o Load columns, remove prefixies in column derivations

…can then do selects on the file, or lookups etc.

View or Read on HASH file shows no data in one or more columns

Also related to the problem below. This happens where columns in a hash file have been moved
around, for instance when creating a new xref hash file. If the columns of the new file are saved, the
DS_METADATA record might have attribute 22, the position indicator, set incorrectly.
When this metadata is loaded into another stage, column data might appear to be missing. To fix this
preferably correct the DS_METADATA record after saving it; or change the Position column in the
hash file stage where the metadata has been loaded. This should avoid having to enter the columns
manually as recommended below.
Read on HASH file when correct key doesn’t find existing record
And other similar problems where the data looks like it’s in the wrong column when viewed in the
DataStage hash file stage.
This is often caused by having loaded the column definitions from DataStage Manager Table
Definitions. The resolution is to delete all the columns in the hash file stage and enter them manually.
If there are too many try just deleting the ones where it goes out of step and re-entering them manually.
Alternatively, re-import the metadata in Manager, then in the job delete the file stage, recreate it and
load the columns from the re-imported metadata (having removed ‘I’-types).

Warning Messages
Warning message in log regarding Phantom processes
For example: DataStage Job 270 Phantom 1364
Program "JOB.1215067440.DT.1362629138.TRANS1": Line 301, Variable previously
undefined. Zero length string used.
The Job number is 270 meaning that under that project directory there will
be a subdirectory called RT_BP270. Under this directory will be the source
code for JOB.1215067440.DT.1362629138.TRANS1. Each transformer will have a
program. In this case you have a transformer called TRANS1. It should be
possible to work out line 301 by looking at the transformer.

09/12/21 Page 13 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Other log files

&PH& at Universe level. Can set up pointer to it called PH:
1. F Ardent DataStage Repository file
2. &PH&
3. D_VOC

./DataStage/DSEngine/errlog

Warning message accessing data from an Oracle file stage

Message: table name not in link???

Normally happens if using user-defined SQL, stage can’t find the table name. Change the user-defined
SQL to be built normally, put the table name into the ‘build’ section, save the stage, then return it to
user-defined SQL

Warning message accessing data from an Oracle file stage

Message: Variables not bound

Due to trying to accumulate data in the SQL in the Oracle stage, using the GROUP BY command.
Might need to feed the relevant data into a hash file first. Amazingly this also works in a container, i.e.
the hash file is built once at the start of the process.

Warning message doing a ‘View data’ on an Oracle file stage

Message:
DSBrowser..ORA_RefVendor: ORA-00932: inconsistent datatypes

This is due to the Oracle table having a datatype different to that defined in the stage Columns. For
instance, the stage might say TIMESTAMP whereas the Oracle table says DATE.

Warning message when running DataStage job

Message:
CustConvert.N_CUSTOMER: [IBM][CLI Driver][AS] SQL0407N Assignment of a NULL value
to a NOT NULL column "*N " is not allowed.
SQLSTATE=23502

This is caused by DataStage reading a file and the record is not on file. The fields used later in the
process then can contain an SQL null. To fix pass the relevant file fields through an IsNull test after
every look-up

NOTE: All fields in a Primary Key in Oracle are mandatory – i.e. can not have a
NULL primary key field

Message:
CustConvertRepeatRows.O_CIO2: [IBM][CLI Driver][AS] SQL0913N Unsuccessful execution
caused by deadlock or timeout. Reason code "TESTCONV1.CUSTO00004".
SQLSTATE=57033

This is caused by:

 their being a problem with the Journal Receiver on the AS400 – the Journal Receiver is full
 or the file has reached its maximum number of allowed records (need to set file to NOMAX)
 or there is a lock on the file on the AS400

09/12/21 Page 14 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Message:
Buffer overflow
Normally caused by a field being too long for its target field

Message:
M0094NSRClaimPaddr..NSR_CLAIM.SourceFile: ds_udtopen() - Unable to connect to server
UniData Client error: call to UniOpenPos returned 20 - Client and server product identifiers
don't match: received The udt counter and returned counter
M0094NSRClaimPaddr..NSR_CLAIM.SourceFile: DSD.UDTOpen GCI $DS.UDTOpen error 24.

Caused by running out of licences on the Unidata server

Message:
Unable to Compile and Catalog DSHELPER subroutine -
aaPopulateXrefFileCM..COMMON_LAND.in

To do with UniData – DSHELPER has to be globally catalogued in the UniData system, then available
from any account

Message:
DA1522_26000_ETL2TransformJob.1522.DA1522_Prepare_Trf.Data: ds_ipcflush() - Error in
mutex_unlock() - Operation not permitted.

To do with locking, maybe lock left at Universe level (LIST.READU) but no longer there at UNIX
level (not in ps –ef). This can happen if a job does “Attempting to Cleanup after ABORT raised in stage” but
there was no reason why it aborted.

Message:
Data has been truncated

To identify field that is too long change output to fixed-length flat file and run the job, then the log
identifies the column!
Killing a process
Find the pid of the process, possibly by doing a LIST.READU
Then kill it using LOGOUT –{pid}
LISTU – shows users logged on
UNLOCK ALL or other option – from UV account, clears all locks
UNLOCK USER 61234 ALL for one specific user (Userno column in LIST.READU)

DOS – useful bits

rmdir {directory name} /s
del D_{file name}

UNIX – useful bits

To ‘kill’ a process running udt:
Find PID, using ‘who’
ps –ef | grep pts/nn or grep PID
kill –2 PID

Unix backspace key gives ^H, type in:

stty erase <BACKSPACE>
and in the .profile put:
stty erase ‘^H’

Repeat commands:

09/12/21 Page 15 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

set –o vi then use normal vi commands, h, j, k, l, / to find, cw etc.

cd - to revert to the previous directory

cd {space} to revert to home directory

Get Unix to finish typing for you (it will fill the unique bit of the file name):
<ESC>\ and file name is completed automatically

Changing the UNIX prompt

export PS1='$PWD > '
export PS1='$ '

Sort output from ls –al by the fifth column:

ls –al | sort –k5

sort {-o output filename} {-t~} –k1.1 {–k3} {input filename}

where –t defines the delimiter, -k field.column is the sort key

Script to remove a list of files or directories (called H_…)

for i in `ls|grep H_` note apostrophe is top left hand key
do
rm -rf $I -rf, all contents and don’t prompt
done

Display files that match ‘a’ OR ‘b’:

Create a file with the text to search for, one item per line
ls –l | fgrep –f {filename}

Display only directories in the current directory:

ls –l | grep ‘^d’ ^d means first character is a d

Displays lines in a file that do NOT have 0 to 9 as the first character:

grep –v ‘^[0-9]’ filename

Displays directories excluding DataStage ones:

ls -l |grep -v 'RT_' |grep -v 'DS_'|grep ^d|pg

Search for a particular text string in a mass of files:

find . –exec grep “string to find” ‘{}’ \; -print

The argument '{}' inserts each found file into the grep command line. The \; argument
indicates the exec command line has ended.

Display all hash files excluding dictionary levels and log files:
ls –l ./HashFiles/ | grep –v ‘_D’ | grep –v ‘LOG’ | pg

Displays lines containing abc OR xyz:

grep -E "abc|xyz" file1

Count number of rows in INPUTFILE_FF starting with ‘20’:

grep ^20 /usr/dstage/data/Basel2/work/DA1842/INPUTFILE_FF | wc -l

df –k . show used and free space in kilobytes

Save and compress a bunch of files, then reverse the process:

09/12/21 Page 16 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

tar –cvf {newname}.tar *{sel criteria}*

compress {newname}.tar
…
uncompress {newname}.tar.Z
tar –xvf {newname}.tar

Useful ORACLE views

There are a number of views you can look at (in the SYS schema)...
USER_TABLES - shows all the tables owned by the schema you are logged in as, ALL_TABLES
shows the tables that you can access, DBA_TABLES shows all tables.
USER_TAB_COLUMNS - shows all the columns on all the tables in your schema, All and DBA are
as above.
USER_OBJECTS - shows all database objects (e.g. tables, packages, functions, views, sequences etc)
in your schema, All and DBA as above.

ORACLE performance
For stats re processes that are running use, or pick out relevant columns:

select * from v$sqlarea order by cpu_time desc

Usefull columns include SQL_TEXT, EXECUTIONS, CPU_TIME, ELAPSED_TIME,

DISK_READS, BUFFER_GETS, ROWS_PROCESSED

SQL
Wildcard character is ‘%’ (matches zero or more characters). Underscore ‘_’ matches 1 character.
Also have the NOT operand.
Comparisons allowed are: =, <>, <, <=, >, >=
/* used to include comments on SQL query page */
Simple SQL commands:
SELECT TABLE_NAME FROM USER_TABLES;
DESC {tablename}; – show colums in the table
SELECT (*) FROM {tablename};
SELECT COL1, COL2, COL3 FROM {tablename};
SELECT NAME FROM SALESPREPS
WHERE REP_OFFICE IS {NOT} NULL;
SELECT NAME, SALES, QUOTA, (SALES – QUOTA) FROM SALESREPS
WHERE SALES < QUOTA;
SELECT A, B, C, AMOUNT FROM ORDERS
WHERE AMOUNT > 2500.00 AND ORDER_NUM > 200200
ORDER BY AMOUNT;
SELECT ORDER_NUM, AMOUNT FROM ORDERS
WHERE AMOUNT BETWEEN 20000.00 AND 29999.99
ORDER BY ORDER_NUM, AMOUNT;
SELECT ORDER_NUM, ORDER_DATE, AMOUNT FROM ORDERS
WHERE REP_OFFICE IN (11, 13, 22);
Select customer called Smithson … or Smithsen …
SELECT COMPANY, CREDIT_LIMIT FROM CUSTOMERS
WHERE COMPANY_NAME LIKE ‘Smiths_n %’;
Allocating table names a letter and using that:
SELECT C.Account_Number,C.Lessee_Name,…etc…,B.Insurance_Indicator
FROM STH_Customers C,STH_Bookings B WHERE C.Account_Number =
B.Account_Number;

09/12/21 Page 17 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

Example using left outer join

/*ECS applications*/
select g.regionname, p.projectid, p.projectver, d.cphnumber
from project p
left outer join projectregions r on p.projectid = r.projectid and
p.projectver = r.projectver
left outer join refgoregion g on r.regioncode = g.regioncode
left outer join projectpartyaddress a on p.projectid = a.projectid
and p.projectver = a.projectver and role = 'appl'
left outer join address d on a.addressid = d.addressid
where schemeid = 'ECS' and r.regiontype = 'L' and
p.projectid not in (select projectid from projectevent
where eventcode in ('APP','ARI','RJC'))
Example using TO_DATE and TRUNC
You need the TO_DATE to tell oracle that it's a date value and what the format is to use. I've also
added a TRUNC around the TIMECREATED to disregard the time element of the column when doing
the evaluation.

SELECT TABLEID,
TIMECREATED,
JOBID
FROM data_control_table
WHERE scheme = 'Aesis'
AND TRUNC(TIMECREATED) = TO_DATE ('08-DEC-03', 'DD-MON-YY')
ORDER BY timecreated,
tableid

Summarising data:
SELECT AVG(100 * (SALES / QUOTA)) FROM SALESREPS;
Average size of an order in the database:
SELECT AVG(AMOUNT) FROM ORDERS
WHERE CUST = 213423;
Total amount of orders by customer (group orders by customer and total):
SELECT CUST, SUM(AMOUNT) FROM ORDERS
GROUP BY CUST;
SELECT REP, CUST, SUM(AMOUNT) FROM ORDERS
GROUP BY REP, CUST;
Minimum and maximum size of an order in the database:
SELECT MIN(QUOTA), MAX(QUOTA) FROM SALESREPS;
Count how many orders in the database (counts number of rows):
SELECT COUNT(*) FROM ORDERS;
SELECT COUNT(AMOUNT) FROM ORDERS WHERE AMOUNT > 25000.00;
Count distinct number of titles:
SELECT COUNT(DISTINCT TITLE) FROM SALESREPS;
For 3 particular sinds count and group by year (APDATE=DD^MMM^YY):
SELECT SUBSTR(APDATE,8,2), COUNT(SUBSTR(APDATE,8,2)) FROM
COMPOSITE_FWPS_AGREEMENT WHERE SIND IN (8,12,29) GROUP BY
SUBSTR(APDATE,8,2);
Updating data in the database:

Inserting data into the database:

INSERT INTO CUSTOMERS (COMPANY, CUST_REP, CUST_NUM, CREDIT_LIMIT)
VALUES (‘Acme Industries’, 109, 2125, 25000.00);
UPDATE CUSTOMERS
SET CREDIT_LIMIT = 75000.00

09/12/21 Page 18 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

WHERE CUST_NUM = 2125;

Delting data from the database:

DELETE FROM CUSTOMERS WHERE CUST_NUM = 2125;

Other commands (briefly):

LOWER(‘example OF-TEXT’) = example of-text
UPPER(‘example OF-TEXT’) = EXAMPLE OF-TEXT
INITCAP(‘example OF-TEXT’) = Example Of-Text
GRANT INSERT Grant permission to insert
ON CUSTOMERS TO {Userid};
GRANT UPDATE, SELECT Grant permission to update or select
ON CUSTOMERS TO {Userid};
grant select on dcspr_request to public
REVOKE INSERT Revoke permission to insert
ON CUSTOMERS FROM {Userid};
REVOKE ALL Revoke all privileges
ON CUSTOMERS FROM {Userid};
CREATE TABLE PRODUCTS Define a new table
(MFR_ID CHAR(3), PRODUCT_ID CHAR(5), DESC VARCHAR(30),
PRICE MONEY, QTY_ON_HAND INTEGER);
CREATE TABLE HCLJEAN AS SELECT * FROM HUB_CODE_LOOKUPS
DROP TABLE PRODUCTS; Delete table
TRUNCATE TABLE PRODUCTS; Clear table

Type of Join Operation Description

Pure Iner Join A AND B Merges only those rows with the same key values in
both input files

Complete Set A OR B Merges all rows from both files

Right and Left Only A NOR B Merges all rows from both files except those rows
with the same key values

Left Outer Join A Merges all rows from the first file (A) with rows from
the second file (B) with the same key

Right Outer Join B Merges all rows from the second file (B) with rows
from the first file (A) with the same key

Left Only A NOT B Merges all rows from the first file (A) except rows
with the same key in the second file (B)

Right Only B NOT A Merges all rows from the second file (B) except rows
with the same key in the first file (A)

NULL handling
In a WHERE clause any condition that does not explicitly mention NULL values automatically
fails if one of the values is NULL. Hence
SALMONELLA_STATUTORY_SPP_FLAG <> 'T' / 'F' will automatically yield FALSE if
SALMONELLA_STATUTORY_SPP_FLAG is a null. If you want to cater for nulls then you
should put:-

where SALMONELLA_STATUTORY_SPP_FLAG IS NULL

09/12/21 Page 19 of 20
/conversion/tmp/scratch/58361818.doc
 DataStage Scribble Sheet

(it's quicker to write too).

In 1, the GROUP BY function does include a grouping by the NULL value for any term, so the
NULL fields are included and counted.

If you want to include nulls in a different way you can also use the function
NVL(field_which_could_be_null, value_to_replace_NULL_with) e.g.

WHERE NVL(SALMONELLA_STATUTORY_SPP_FLAG, 'X') = 'X'

Odds and sods

 SQL guide web site: http://www.canberra.edu.au/~sam/whp/sql-guide.html

09/12/21 Page 20 of 20
/conversion/tmp/scratch/58361818.doc