DataStage Hints

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 Tab Line Feed Carriage Return Escape Space Oracle null written to hash file Text mark Sub-multi-value mark Multi-value mark Attribute mark Oracle null Pick null – i.e. “”

CHAR() 09 10 13 27 32 128 25 1 252 2 53 25 4 See below 0

Hex 09 0A 0D

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_VA NVL(SOURCE_SYSTEM_K4_VALUE,-999)=:4 LUE,-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: The file is called .odbc.ini

cd `cat /.dshome`

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

16/05/11

Page 1 of 20 /opt/scribd/conversion/tmp/scratch6111/58361818.doc


Command to find number of files that can be opened: To increase number of files that can be opened: Place this after the umask command

ulimit –aH ulimit –n 512

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} {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 , 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: Then to revert:

PTERM -CASE NOINVERT PTERM -CASE INVERT

16/05/11



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 (<>), 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 <> NE 'YES' THEN GO FINISH 006: COMO APPEND CLEAR.HVDC.FILES 007: DATE 008: ! who am I 003: AE IACS.ST<> <> 004: DATA 101 005: DATA R 343963 008: DATA FI 010: SELECT IACS.ST<> "<>" 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 "," <> 019: COMO OFF 021: FINISH: *

CREATE.FILE DIR filename CREATE.FILE filename DYNAMIC

creates UNIX directory creates a dynamic hashed file

Universe debug tool – RAID / [string] [line] B B* C D D* [line]G L variable M variable=VALUE M

Q [n] S [n] S*

T [line] W

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

16/05/11



[line] Z [n] variable / variable ! STRING

Displays the next 10 lines of source code Subtracts n from the current line Displays the value of variable 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 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

16/05/11



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 • • • • • • •

• • • • • • • • • • • • • • • •

• •

16/05/11



<20> = Nullable <21> = Key <22> = Display length <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 item in file DS_JOBOBJECTS J\nn\V0S… <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 • • • • • • • •

•

• • • • • • • • • • • • • • •

•

• • • • • • • • •

•

• • • • •

•

• • • • •

•

• • •

•

16/05/11



•

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 <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}; = 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 • • •

•

• • • • • • • •

•

• • • • •

•

• • •

• • • • • • • • •

•

• • •

•

• • •

•

•

• • • • • • •

16/05/11



<15> = SQL type code, defined as above <16> = Length <17> = Scale <18> = Nullable – 0 or 1 <19> = Key – 1 or 0 <20> = Display length

• • • • • • •

•

number) •

‘S’ingle or ‘M’ulti-valued <98> = text / number of fields

<21> = Association Only displayed if <9> = 1 <22> = Position (attribute Only displayed if <9> = 1 <23> = Attribute type – Only displayed if <9> = 1

•

<102> = Dunno, set to multi-

•

valued zeros "DISPLAY_NUMERIC"

v7.5: Level? <103> = text: v7.5

•

<104> = set to multi-valued

•

zeros <108> = ditto <110> = ditto

• •

default=0"

<111> = text: "DISPLAY" v7.5 <113> = text: "zoned, 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

16/05/11



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 $INCLUDE $INCLUDE $INCLUDE

DSINCLUDE DSINCLUDE DSINCLUDE DSINCLUDE

DSD_STAGE.H JOBCONTROL.H DSD.H 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)

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

16/05/11



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 E:\DSProjectBKP\DMSPresentation\%DAYNUM%_%DSPROJECT%.dsx E:\DSProjectBKP\DMSPresentation\%DAYNUM%_%DSPROJECT%.txt

%DSPROJECT% /V >

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) 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"

16/05/11



or

Script<-1> Script<-1> Script<-1> Script<-1> Script<-1> Script<-1> Script<-1> Script<-1>

= = = = = = = =

"user f803163 phant0m" "lcd /radf/sinay/uat/temp" "cd ../erdp" "mdelete ":WILDCARD "put ":FILENAME:VERSION "mput ":WILDCARD "quote site chmod 646 ":FILENAME:VERSION "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 Sets the DataStage internal variable UserStatus, for instance with the output from an Oracle COUNT command Feed through the contents of UserStatus as a parameter Returns the number of rows processed in a link Returns the number of rows processed in a link in another job

DSGetJobInfo(DSJ.ME,DSJ.JOBNAME) SetUserStatus( inputlinkname.ROW_COUNT)

From a sequencer: click on Insert parameter value Find the jobname where user status was set Click on $UserStatus DSGetLinkInfo(DSJ.ME," TransformName","LinkName", DSJ.LINKROWCOUNT)

Call a DataStage routine which does the following: 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

Equ Equ Equ Equ Equ Equ Equ Equ Equ

To To To To To To To To To

DSJS.RUNOK DSJS.RUNWARN DSJS.RUNFAILED DSJS.VALOK DSJS.VALWARN DSJS.VALFAILED DSJS.RESET DSJS.CRASHED DSJS.STOPPED

Equ DSJS.NOTRUNNABLE To Equ DSJS.NOTRUNNING To

0 ;* This is the only status that means the job is actually running 1 ;* Job finished a normal run with no warnings 2 ;* Job finished a normal run with warnings 3 ;* Job finished a normal run with a fatal error 11 ;* Job finished a validation run with no warnings 12 ;* Job finished a validation run with warnings 13 ;* Job failed a validation run 21 ;* Job finished a reset run 96 ;* Job has crashed 97 ;* Job was stopped by operator intervention (can't tell run type) 98 ;* Job has not been compiled 99 ;* Any other status

16/05/11



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 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()

16/05/11



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 1 4 20 50 80 1628

d:\FTPData\MigrationDev\DUPLICATES

30

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

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.

16/05/11



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.

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:

16/05/11



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 " is not allowed. to a NOT NULL column "*N 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  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.

16/05/11



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 and in the .profile put: stty erase ‘^H’ Repeat commands: set –o vi

cd cd {space}

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

to revert to the previous directory to revert to home directory

Get Unix to finish typing for you (it will fill the unique bit of the file name): \ 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}

16/05/11



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: 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

16/05/11



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;

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

16/05/11



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 WHERE CUST_NUM = 2125; Delting data from the database: DELETE FROM CUSTOMERS WHERE CUST_NUM = 2125; Other commands (briefly): LOWER(‘example OF-TEXT’) = UPPER(‘example OF-TEXT’) = INITCAP(‘example OF-TEXT’) = GRANT INSERT ON CUSTOMERS TO {Userid}; GRANT UPDATE, SELECT ON CUSTOMERS TO {Userid};

example of-text EXAMPLE OF-TEXT Example Of-Text Grant permission to insert Grant permission to update or select

grant select on dcspr_request to public

Revoke permission to insert REVOKE INSERT ON CUSTOMERS FROM {Userid}; Revoke all privileges REVOKE ALL ON CUSTOMERS FROM {Userid}; Define a new table CREATE TABLE PRODUCTS (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; TRUNCATE TABLE PRODUCTS; Type of Join

Operation

Delete table Clear table

Description

16/05/11



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 (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

16/05/11


DataStage Hints

Recommend Documents