• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

String Functions in DataStage

String Functions

  1. Left
  2. Right
  3. Substring
  4. Field

 

EX:-

  1. Mind Quest

L (7) = Mind Que

R(3) = Que                                  (on Sub String (5) 3)

R (L (7), 3)

 

  1. Mathe ndticks

R (L (7), 2) = ma (or)  L(R ()0,2) = ma

 

  1. Create an text file

HINVE 23409 CID 454321200802DOL

TPID1234                       5               12

TPID2345                        6              13

TPID3456                       7              14

 

Screenshot_15

 

Properties à Browse for file 7 file = D :/ shilpa / invoice.txt7GO to Columns7Loading a text file with single record

 

Column Name                          SQL Type                     Scale

Records                                    Varchar                           255

Now, click on view data

  • Transformer7Properties7Create 2 new columns

 

  1. Type char 1
  2. Data varchar 100

 

  • Now drag in records to data

Screenshot_17

 

  • Now create an stage variable

type  —   0   —   varchar  —  “1”

  • Stage Variable left (in. records, i) type
  • Compile and RUN
  • Display all the 1st Characters:-

 

 

Type                          Data
H HINVC  23409CID0454321200802000L
T
T
H

 

Add Some More Column Names

In Voice no       Varchar            10
Cust id                 Varchar 10
Bill date              Varchar 10
Currency              Varchar 10
Product id            Varchar 10
Quantity               Varchar 10
Net amount         Varchar 10

 

Add new stage Variables

Right (left (in . records, 10), 9)  = in v no

Right (left (in . records, 19), 9)  = cid

Left(Right (in . records, 11), 8)  = bd

Right (in . records,  3)  = curr

Right (left (in . records, 8), 7)  = p id

Right (left (in . records, 17), 1)  = Quant

Right (left (in . records, 26), 2)  = Price

 

Click on Constraint

Constraint     :   type = “H”

Output 

Type         data                 invoice num    Custid                bill date               currency

H                HWCNC                 INVC         CID 454321   20080203               DDl

23409 CID                 23409

H

H

H

Reading Comma Separated values of variable length

  • Create an emp table with fields

E ID,   E NAME,     STATE

10, abc, ap

20,def, ap

30,ghi,ap

Create an EMP table with Spaces before, after in between and some Special Characters

EID, E NAME,STATE

10, @abc, ap

20, def @,ap

30, ghi@, ap

Functions

  1. TRIMB – Removes after Space
  2. TRIMF – Removes after Space
  3. STRIP WHITE SPACES – Removes before, After , in between spaces
  4. COMPACTWHITE SPACES
  • Remove single Spaces
  • IF Multiple Spaces are there, Removes in between spaces and leaves space

EX:- MIND Q UEST

MIND Q UEST

  1. TRIM – Removes Special Characters

To Eliminate Comma, Spaces , Special character using Functions :-

 

In     —   out          —         ds link 3

 

S.F  7  Transform  1   7    Transform 2   7     Data set

 

               ↓

File = D: / Shilpa / emp.txt

 

7Read it with Single Column  (that is columns)   (rec integer 255)

7first line is column Name = True

Transformer 1 :-   (Removing Comma)

Click on OUT7Click on  load column Definitions Symbol7Select EMP.txt

 

Field  (in.  rec,’,’, 1)  = E id

Field  (in.  rec,’,’, 2)  = E Name

Field  (in.  rec,’,’, 3)  = State

[Syntax :- field  (% String %, %delimiter%,  % user name %)]àCompile and RUN

 

E id               E NAME                State

10                   abc                          ap

20                    def                           ap

30                    ghi                          ap

Transformation 2  :- (Eliminating Spaces, Special charities )

  • Properties
  • Up case (Trim (Strip white spaces (Dut E name),”@”)) = E name
  • Compile and RUN

 

E id               E NAME                State

10                   abc                          ap

20                    def                           ap

ghi                   ap

Changing the format of date using Type conversion and Date and Time function 

  • Task 1 

We have Date as   2008-01-03  (YYYY-MM-DD) 00:00:00 (Times stamp)Using field and type conversion functions change it to DD/MM/YY

 

In                                                    out

Oracle enterprise     7   Transformation  7 -Data set

 

     ↓

Load an emp file

     ↓

Transformer7Properties7Drag in to out7Create 3 New stage variables7Using field functions

 

Field (Times stamp To Date (in. Hire data),’_’,3) –DD

Field (Times stamp To Date (in. Hire data),’_’,2) –MM

Field (Times stamp To Date (in. Hire data),’_’,1) –YY

 

(or)

 

  • Using Type and Date conversion functions

Month Day from Date  (Times stamp To Date (in. Hire data)) –DD

Month  from Date  (Times stamp To Date (in. Hire data)) –MM

Month Year from Date  (Times stamp To Date (in. Hire data)) –YY

     ↓

 

Now in hiredate

 

Screenshot_18

DD 7 Concat 7 dd: 7dd :’/’ 7 Contact 7 DD:’/’ MM:’/’:YY

 

  • Task 2 

Create a new  column

COMM – VAL    varchar  8

7change Hire data type to varchar

7 Create  another Stage variable Comm is varchar 10

If is null (in. comm) then “Null” else left  (in. comm., 2)

(that is, if Comm in NULL displays Null, else display 1st 2 characters )

7Compile and RUN

Parameters 

EX:-

Int a = 6, b= 5, c                                              Compile time

C= a+b

7Here we are Passing values through Compile time Known as “Static Binding”.

Ex:-  int a, b, c;

<in >>a>>b;                     Run time

C = a+b

7Passing Value at Run time is known as “Dynamic binding”

7Till Now , we have used Static Binding, Now, we  See Dynamic Binding using Parameterization.

Parameterization

It is a technique for passing values at run time.

7 It is more advantageous

Screenshot_19

 

  • Local parameters are job Specific p to 7.5 * 2
  • Global parameters are used in entire project
  • Reusability in project through a technique called Parameterization in 8.0.1
Interested in mastering DataStage? Learn more about DataStage Tutorial in this blog post.

Advantages :-

  1. Local parameters can be Reused
  2. We can not only pass/ assign / value to parameter but can pass multiple values.

Using Parameters

Oracle Enterprise             Transformer             Data set

Properties 7 Read Mode = Table   Table = EMP7Columns à load 7 Come to properties 7 click on password7Insert job parameter7New   (or)7Click on job properties Symbol7Parameters

 

Parameter name                prompt  Type                   Default value
UID                                         user id                   String                     Sc
PWD                                         password           Encrypted            Tiger
CS                                               Server                     String              Oracle

 

ok7click on password on left side7insert job parameter, Password  = # PWD , User = #UID#, Remote Server  =# CS#

7Transformer 7 properties 7 Drag in to out 7 click on Constraint7Double click 7 Right click 7 job parameter 7 New

 

DNO                               Department               list              10 (Enter) Add

20 (Enter)Add

30 (Enter)Add

7ok7Right click 7job parameter7Select DNO7DNO = in. DEPT NO7 Ok7Ok

 

Data set 

  • File àinsert job parameter à New

 

DRIVE                   IP                                  String                D:1

FOLDER                FOLDER                      String                  Shilpal

TRG_FILE            TARGET                        String                  Parameter. Ds

 

7ok

7# DRIVE ## FOLDER ## TRG _ FILE #

7Compile

  • RUN
  • Dering RUN time it asks for Dept no

User id = Scott

Dept no = 20

  • Display data with Dept no = 20

Out put :– During RUN

 

Name Value
User id Scott
password Tiger
Server Oracle
dependent 30, 20, 10
input D:1/
folder Shilpal/
target Param . ds

 

  • Reuse:-  (To reuse in Multiple job)

Click on  job properties 7 parameter 7 Select UID, PWD, CS7click on create parameter Set 7 parameter Set Name = Oracle – shilpa7ok 7 Save it in Table Definitions 7 ok

7Now, when ever we create any other  job, we can directly call oracle – shilpa parameter set,

  • Oracle
  • Properties
  • Password = Set to Default

User = Set to Default

Remote Server = Set to Default

password = # oracle _shilpa. PWD #

User = # oracle – shilpa . UID #

Remote Server = # oracle – shilpa.cs #

  • Editing :-

View 7Repository7Table Definition 7oracle – shilpa 7 Double click 7 Values

 

Value file name                       UID                 PWD              CS

Development                            SCOTT             Tiger          oracle

Testing                                          abcd           abcd                renu

Production                                    shilpa            shilpa            Star

  • Ok
  • Compile and RUN

Output

Name                                                           Value

Oracle – shilpa parameters                    (AS predefined )   testing production development

Name Value
User id Scott
password Tiger
Server Oracle
dependent 30, 20, 10
input D:1/
folder Shilpal/
target Param . ds

Transformation Execution order

  • Stage variables are Evaluated before the data is moved to the link (that is Transformer)
  • Derivation are Evaluated just before the data is moved to target
  • In stage Variable, initialization is possible
  • Stage Variables are more advantages

 

What is Transformer Execution order 

Screenshot_20

 

 Calculating Experience

 

Screenshot_21

 

Experience integer 7
Exp-months integer 7
Exp-days integer 7
Week-join integer 7
Quarter-join integer 7

 

Task is to find out the experience in years, months, days, week of joining , Quarter

Of joining

Create 2 stage variable

Exp          integer         7

Month     integer         7

Exp = year from Date  (current Date ()) –  Year from Date  (Times stamp to date (in. Hire date))

Month = Month from Date  (Times stamp to date (in. Hire date))

  • Now – – > exp = experience
  • Exp * 12 = exp – month
  • Days Since from Date (current Date) Times stamp to date (in. Hire date) = Exp- days
  • Year week from Date (Times stamp to date (in. Hire date)) = week – join
  • If month < =3 then1 else if Month >=4 and Month <=6

Then 2 else if month > = 7 and Month <=8 then 3 else 4

 

7Compile and RUN

Transformation with Sorting 

Create an file

 

E id E name Account
111 Suman Savings
222 Kumar loans
111 Suman current
333 uma loans
111 suman credit
222 kumar savings
333 uma insurance
111 suman insurance
222 kumar current

 

Case 1

Screenshot_22

 

Transformer

Stage Variables

Previous key        0      Integer/ Tiny int      1

New key             “N”     char                          1

Accents              “Y”       Varchar               255

 

Map the key change to prev key

In. key change –prev key

If prevkey = 1 then “y” then in. account           –accents

Else accounts :”,”: in. account

 

  • Create a new Column :- 1 ACNT – ALL
  • Drag accents to ACNT – ALL
  • Accents – ACNT – ALL
  • Now, Compile and RUN

Output 

E ID          E NAME                   Account             key                a

 

Case (ii) 

Sequential file —–Sort—-Transformer——Sort——–Data set

 

Create an new Stage variable 

If new accent  =”y” then 1 else cnt+1 –– – >cnt

  • Sort

Key  = Count / E id

Sort order = Descending

Output

 

Data set

PartitioningàPartition type = HashàClick on cidàPerform sort, Stable, unique

For indepth understanding of DataStage click on

Summary
Review Date
Reviewed Item
String Functions in DataStage
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on String Functions in DataStage"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.