You must assess your data.

Data Stage jobs can be quite complex and so it is advisable to

consider the following before starting a job:

• The number and type of data sources. You will need a stage for each data

source you want to access. For each different type of data source you will need a

different type of stage.

• The location of the data. Is your data on a networked disk or a tape? You may

find that if your data is on a tape, you will need to arrange for a custom stage to

extract the data.

• Whether you will need to extract data from a mainframe source. If this is the

case, you will need Enterprise MVS Edition installed and you will use mainframe

jobs that actually run on the mainframe.

• The content of the data. What columns are in your data? Can you import the

table definitions, or will you need to define them manually? Are definitions of

the data items consistent between data sources?

• The data warehouse. What do you want to store in the data warehouse and

how do you want to store it?

To assign a null value to a variable, use this syntax:

variable = @NULL

To assign a character string containing only the character used to represent the null

value to a variable, use this syntax:

variable = @NULL.STR

Errors that occur as the files are loaded into Oracle are recorded in the sqlldr log file.

Rejected rows are written to the bad file. The main reason for rejected rows is an integrity

constraint in the target table; for example, null values in NOT NULL columns, nonunique

values in UNIQUE columns, and so on. The bad file is in the same format as the input data file

• String operators for:

• Concatenating strings with Cat or :

• Extracting sub strings with [ ]

Hello. : My Name is : X : . What’s yours?

... evaluates to:

Hello. My name is Tarzan. What’s yours?

☻Page 95 of 210☻

Field Function: Returns delimited substrings in a string

Returns delimited substrings in a string

MyString = "London+0171+NW2+AZ"

SubString = Field(Mystring, "+", 2, 2)

* returns "0171+NW2"

A=12345

A[3]=1212

The result is 121212.

MyString = "1#2#3#4#5"

String = Fieldstore (MyString, "#", 2, 2, "A#B")

* above results in: "1#a#B#4#5"

Operator Relation Example

Eq or = Equality X = Y

Ne or # or >< or <> Inequality X # Y, X <> Y

Lt or < Less than X < Y

Gt or > Greater than X > Y

Le or <= or =< or #> Less than or equal to X <= Y

Ge or >= or => or #< Greater than or equal to X >= Y

You cannot use relational operators to test for a null value. Use the IsNull function

instead.

Tests if a variable contains a null value.

MyVar = @Null ;* sets variable to null value

If IsNull(MyVar * 10) Then

* Will be true since any arithmetic involving a null value

* results in a null value.

End

IF Operator:

Assigns a value that meets the specified conditions

• Return A or B depending on value in Column1:

If Column1 > 100 Then "A" Else "B"

Function MyTransform(Arg1, Arg2, Arg3)

* Then and Else clauses occupying a single line each:

If Arg1 Matches "A..."

Then Reply = 1

☻Page 96 of 210☻

Else Reply = 2

* Multi-line clauses:

If Len(arg1) > 10 Then

Reply += 1

Reply = Arg2 * Reply

End Else

Reply += 2

Reply = (Arg2 - 1) * Reply

End

* Another style of multiline clauses:

If Len(Arg1) > 20

Then

Reply += 2

Reply = Arg3 * Reply

End

Else

Reply += 4

Reply = (Arg3 - 1) * Reply

End

Return(Reply)

Calls a subroutine. Not available in expressions.

Syntax

Call subroutine [ ( argument [ ,argument ] … ) ]

Subroutine MyRoutineA(InputArg, ErrorCode)

ErrorCode = 0 ;* set local error code

* When calling a user-written routine that is held in the

* DataStage Repository, you must add a "DSU." Prefix.

* Be careful to supply another variable for the called

* routine's 2nd argument so as to keep separate from our own.

Call DSU.MyRoutineB("First argument", ErrorCodeB)

If ErrorCodeB <> 0 Then

... ;* called routine failed - take action

Endif

Return

Special DataStage BASIC Subroutines

DataStage provides some special DataStage subroutines for use in a before/after

subroutines or custom transforms. You can:

• Log events in the job's log file using DSLogInfo, DSLogWarn, DSLogFatal,

and DSTransformError

• Execute DOS or DataStage Engine commands using DSExecute

All the subroutines are called using the Call statement.

Logs an information message in a job's log file.

☻Page 97 of 210☻

Syntax

Call DSLogInfo (Message, CallingProgName)

Example

Call DSLogInfo("Transforming: ":Arg1, "MyTransform")

Example

Call DSLogInfo("Transforming: ":Arg1, "MyTransform")

Date( ) :

Returns a date in its internal system format.

This example shows how to turn the current date in internal form into a

string representing the next day:

Tomorrow = Oconv(Date() + 1, "D4/YMD") ;* "1997/5/24"

Ereplace Function:

Formats data for output.:

Replaces one or more instances of a substring.

Syntax

Ereplace (string, substring, replacement [ ,number [ ,begin] ] )

MyString = "AABBCCBBDDBB"

NewString = Ereplace(MyString, "BB", "")

* The result is "AACCDD"

= FMT("1234567", "14R2") X = "1234567.00"

X = FMT("1234567", "14R2$,")X =

FMT("12345", "14*R2$,")

X = " $1,234,567.00"

X = FMT("1234567", "14L2") X = "1234567.00"

X = FMT("0012345", "14R") X = "0012345"

X = FMT("0012345", "14RZ") X = "12345"

X = FMT("00000", "14RZ") X = " "

X = FMT("12345", "14'0'R") X = "00000000012345"

X = FMT("ONE TWO THREE", "10T") X = "ONE TWO ":T:"THREE "

X = FMT("ONE TWO THREE", "10R") X = "ONE TWO TH":T:"REE "

X = FMT("AUSTRALIANS", "5T") X = "AUSTR":T:"ALIAN":T:"S "

☻Page 98 of 210☻

X = FMT("89", "R#####") X = " 89"

X = FMT("6179328323", "L###-#######") X = "617-9328323"

X = FMT("123456789", "L#3-#3-#3") X = "123-456-789"

X = FMT("123456789", "R#5") X = "56789"

X = FMT("67890", "R#10") X = " 67890"

X = FMT("123456789", "L#5") X = "12345"

X = FMT("12345", "L#10") X = "12345 "

X = FMT("123456", "R##-##-##") X = "12-34-56"

X = FMT("555666898", "20*R2$,") X = "*****$555,666,898.00"

X = FMT("DAVID", "10.L") X = "DAVID....."

X = FMT("24500", "10R2$Z") X = " $24500.00"

X = FMT("0.12345678E1", "9*Q") X = "*1.2346E0"

X = FMT("233779", "R") X = "233779"

Date Conversions

The following examples show the effect of various D (Date) conversion codes.

Conversion Expression Internal Value

X = Iconv("31 DEC 1967", "D") X = 0

X = Iconv("27 MAY 97", "D2") X = 10740

X = Iconv("05/27/97", "D2/") X = 10740

X = Iconv("27/05/1997", "D/E") X = 10740

X = Iconv("1997 5 27", "D YMD") X = 10740

X = Iconv("27 MAY 97", "D

DMY[,A3,2]")

X = 10740

X = Iconv("5/27/97", "D/MDY[Z,Z,2]") X = 10740

X = Iconv("27 MAY 1997", "D

DMY[,A,]")

X = 10740

X = Iconv("97 05 27", "DYMD[2,2,2]") X = 10740

Date Conversions

The following examples show the effect of various D (Date) conversion codes.

Conversion Expression External Value

X = Oconv(0, "D") X = "31 DEC 1967"

X = Oconv(10740, "D2") X = "27 MAY 97"

X = Oconv(10740, "D2/") X = "05/27/97"

X = Oconv(10740, "D/E") X = "27/05/1997"

X = Oconv(10740, "D-YJ") X = "1997-147"

X = Oconv(10740, "D2*JY") X = "147*97"

☻Page 99 of 210☻

X = Oconv(10740, "D YMD") X = "1997 5 27"

X = Oconv(10740, "D

MY[A,2]")

X = "MAY 97"

X = Oconv(10740, "D

DMY[,A3,2]")

X = "27 MAY 97"

X = Oconv(10740,

"D/MDY[Z,Z,2]")

X = "5/27/97"

X = Oconv(10740, "D

DMY[,A,]")

X = "27 MAY 1997"

X = Oconv(10740,

"DYMD[2,2,2]")

X = "97 05 27"

X = Oconv(10740, "DQ") X = "2"

X = Oconv(10740, "DMA") X = "MAY"

X = Oconv(10740, "DW") X = "2"

X = Oconv(10740, "DWA") X = "TUESDAY"

OpenSeq ".\ControlFiles\File1" To PathFvar Locked

FilePresent = @True

End Then

FilePresent = @True

End Else

FilePresent = @False

End

Example

This example shows how a before/after routine must be declared as a subroutine

at DataStage release 2. The DataStage Manager will automatically ensure this

when you create a new before/after routine.

Subroutine MyRoutine(InputArg, ErrorCode)

* Users can enter any string value they like when using

* MyRoutine from within the Job Designer. It will appear

* in the variable named InputArg.

* The routine controls the progress of the job by setting

* the value of ErrorCode, which is an Output argument.

* Anything non-zero will stop the stage or job.

ErrorCode = 0 ;* default reply

* Do some processing...

...

Return

MyStr = Trim(" String with whitespace ")

* ...returns "String with whitespace"

MyStr = Trim("..Remove..redundant..dots....", ".")

☻Page 100 of 210☻

* ...returns "Remove.redundant.dots"

MyStr = Trim("Remove..all..dots....", ".", "A")

* ...returns "Removealldots"

MyStr = Trim("Remove..trailing..dots....", ".", "T")

* ...returns "Remove..trailing..dots"

This list groups BASIC functionality under tasks to help you find the right statement

or function to use:

• Compiler Directives

• Declaration

• Job Control/Job Status

• Program Control

• Sequential Files Processing

• String Verification and Formatting

• Substring Extraction and Formatting

• Data Conversion

• Data Formatting

• Locales

Function MyTransform(Arg1)

Begin Case

Case Arg1 = 1

Reply = "A"

Case Arg1 = 2

Reply = "B"

Case Arg1 > 2 And Arg1 < 11

Reply = "C"

Case @True ;* all other values

Call DSTransformError("Bad arg":Arg1, "MyTransform"

Reply = ""

End Case

Return(Reply)