Datastage Universe Quick Refrence

================================================================
VOC
================================================================
VOC is uniVerse system table.
VOC contains all file pointers, commands (called verbs) and misc stuff.
&PH& (phantom history)
&SAVEDLISTS& (save lists history)
&ED& (edit macros)
&COMO& (COMO history)

================================================================
TCL History
================================================================
.L (list history)
.? (help on history commands)
.X3 (execute the 3rd command in stack)
.D3 (delete 3rd command in stack)
.R3 (recall the 3rd command to the top of the stack)
.C/DS/DSR/ (change DS to DSR)
.S DS.3 (save command to VOC as a sentence)
ED VOC DS.3 (edit the VOC record DS.3)

================================================================
ED commands
================================================================
EX (exit)
2 (goto line 2)
HELP R (help on commands which start with R)
B (goto to bottom or last line)
T (goto to top)
P (page or list the next 20 lines)
PP (center the current line on the screen)
L DS (locate the word DS)
D3 (deletes next 3 lines)
DUP (duplicates line)
<> (marks block as current line)
COPY (copies block to current location)
I (insert lines until you insert a blank line)
C/DS/DSR/ (change DS to DSR on the current line)
R/DS/DSR/ (does same as above)
R DS (replaces whole line with DS)
C (does last change again)
C13/DS/DSR/ (change DS to DSR on the next 13 lines)
C13,DS,DSR, (does same as above)
C999999/DSR/smoe/G (changes DSR to smoe on all lines)
(the G says globally on a line)
(meaning multiple occurances on a line)
FI (files or writes to disk and exits)
FI DS (files this record as DS)
LOAD DSR.BP READ.INV (loads record READ.INV from file DSR.BP
into this record. Will prompt for line numbers)

================================================================
Dictionaries
================================================================
Dictionaries describe data.
Each uniVerse file has a dictionary side and a data side.
The data side is the default for most commands.

LIST DICT KD_MD_COLUMNS (list dictionary side of hash file KD_MD_COLUMNS)

ED DICT KD_MD_COLUMNS @ID (edit dictionary item @ID)

1 -> D (says this is a D type dictionary)
2 -> 0 (@ID is always field 0)
3 -> (oconv)
4 -> KD_MD_COLUMNS (column heading)
5 -> 10L (length plus justification)
6 -> S (single or multivalued)
(@ID is always single valued)

ED DICT KD_MD_COLUMNS DSN (edit dictionary item DSN)

1 -> I (says this is a I-Descriptor)
2 -> FIELD(@ID, @TM, 2) (part 2 of a compound key)
3 -> (oconv)
4 -> DSN (column heading)
5 -> 15L (length plus justification)
6 -> S (single or multivalued)

@ID (the default key on a file)
@KEY (a phrase listing all key fields)
@ (a phrase listing fields in default listing)
(add @KEY fields and ID.SUP if @KEY exists)

@INSERT (default fields for INSERT command)
@REVISE (used by the REVISE command)

CD KD_MD_COLUMNS (compile DICT if you have I-Descriptors)

================================================================
SELECT lists
================================================================
SELECT KD_MD_SYSTEM (creates an active list of IDs)
LIST KD_MD_SYSTEM (uses active select list)

SSELECT KD_MD_SYSTEM (creates sorted lists of IDs)
SAVE.LIST DS (saves list of IDs in &SAVEDLISTS&)

EDIT.LIST DS (allows you to change list of IDs)
ED &SAVEDLISTS& DS (same as above)

DELETE.LIST DS (deletes saved list)

GET.LIST DS (activates a saved list)
LIST KD_MD_SYSTEM (uses active save list)

================================================================
UV BASIC
================================================================
CREATE.FILE DS.BP 19 (do once. Type 19 is a UNIX dir with LONGNAMES)
ED DS.BP MY.BASIC.PROG (MY.BASIC.PROG is a record within DS.BP)
BASIC DS.BP MY.BASIC.PROG (to compile)
RUN DS.BP MY.BASIC.PROD (to run program)

also
UV.VI DS.BP MY.BASIC.PROG (to run UNIX vi on your program)

HELP BASIC OCONV (help on basic function OCONV)

================================================================
COMO files
================================================================
COMO ON DS (creates record named DS in &COMO&)

--- do some commands here ---
LIST VOC
.L

COMO OFF (ALWAYS turn como off)

ED &COMO& DS (all commands and output in here)

================================================================
Examples
================================================================
CREATE.FILE MYBP 19

ED MYBP PRINT.WHO
print @WHO
end

BASIC MYBP PRINT.WHO
RUN MYBP PRINT.WHO

================================================================

ED DICT MYBP LINES

1 -> I (says this is a I-Descriptor)
2 -> DCOUNT(@RECORD, @FM) (counts number of lines in a program)
3 -> (oconv)
4 -> Lines (column heading)
5 -> 5R, (length plus justification)
6 -> S (single or multivalued)


LIST MYBP TOTAL LINES

ED DICT MYBP SIZE

1 -> I (says this is a I-Descriptor)
2 -> len(@RECORD) (counts number of bytes in a program)
3 -> (oconv)
4 -> Bytes (column heading)
5 -> 5R, (length plus justification)
6 -> S (single or multivalued)

LIST MYBP TOTAL SIZE

================================================================

CREATE.FILE MY.INVOICE
Type=18
Modulo=11
Separation=2

ED MYBP INVOICE.ADD
================================================================

open 'MY.INVOICE' to INVOICE else stop

cust.id='BELLSOUTH'
prod.id1='111'
qty1=2
price1=10.00
cost1=8.00

inv.rec=''
inv.rec<2>=cust.id
inv.rec<6>=@date

line.item=1
inv.rec<1, line.item>=prod.id1
inv.rec<3, line.item>=qty1
inv.rec<4, line.item>=iconv(price1, 'MD2')
inv.rec<5, line.item>=iconv(cost1, 'MD2')

prod.id2='222'
qty2=4
price2=7.87
cost2=3.33

* better way
prod.attr=1
qty.attr=3
price.attr=4
cost.attr=5

line.item=2
inv.rec<prod.attr, line.item>=prod.id2
inv.rec<qty.attr, line.item>=qty2
inv.rec<price.attr, line.item>=iconv(price2, 'MD2')
inv.rec<cost.attr, line.item>=iconv(cost2, 'MD2')

prod.id3 ='223'
qty3     =5
price3   =1.21
cost3    =3.22

line.item=3
inv.rec<prod.attr, line.item>=prod.id3
inv.rec<qty.attr, line.item>=qty3
inv.rec<price.attr, line.item>=iconv(price3, 'MD2')
inv.rec<cost.attr, line.item>=iconv(cost3, 'MD2')

write inv.rec on INVOICE, '5'

end

BASIC MYBP INVOICE.ADD
================================================================

RUN MYBP INVOICE.ADD
================================================================

LIST MY.INVOICE
================================================================

ED DICT MY.INVOICE PROD.ID
================================================================

1 -> D (says this is a D type dictionary)
2 -> 1 (field number)
3 -> (oconv)
4 -> PRODUCT (column heading)
5 -> 10L (length plus justification)
6 -> M (single or multivalued)
7 -> ProdMv (association)

ED DICT MY.INVOICE CUST.ID
================================================================

1 -> D (says this is a D type dictionary)
2 -> 2 (field number)
3 -> (oconv)
4 -> CUSTOMER (column heading)
5 -> 10L (length plus justification)
6 -> S (single or multivalued)

ED DICT MY.INVOICE QTY
================================================================

1 -> D (says this is a D type dictionary)
2 -> 3 (field number)
3 -> (oconv)
4 -> Qty (column heading)
5 -> 5R (length plus justification)
6 -> M (single or multivalued)
7 -> ProdMv (association)

ED DICT MY.INVOICE PRICE
================================================================

1 -> D (says this is a D type dictionary)
2 -> 4 (field number)
3 -> MD2 (oconv)
4 -> PRICE (column heading)
5 -> 12R (length plus justification)
6 -> M (single or multivalued)
7 -> ProdMv (association)

ED DICT MY.INVOICE COST
================================================================

1 -> D (says this is a D type dictionary)
2 -> 5 (field number)
3 -> MD2 (oconv)
4 -> COST (column heading)
5 -> 12R (length plus justification)
6 -> M (single or multivalued)
7 -> ProdMv (association)

ED DICT MY.INVOICE SOLD
================================================================

1 -> D (says this is a D type dictionary)
2 -> 6 (field number)
3 -> D4- (oconv for date field)
4 -> SOLD (column heading)
5 -> 12R (dates right justified)
6 -> S (single or multivalued)
7 -> (no association on singlevalued fields)

================================================================
locating multivalued data by ascending left justified
 field 1 is sorted
 fields 2 thru 6 are controlled by field 1
 when field 1 gets a new value then so does fields 2 thru 6
================================================================

locate new.param in params<1> by 'AL' setting mv.no then
   if new.default <> params<2, mv.no> and params<2, mv.no> <> '' then
       params<3, mv.no>=new.default ; * last default.value
       params<4, mv.no>='Y' ; * new.default does change
   end else
       params<2, mv.no>=new.default ; * if null overwrite
   end
   params<6, mv.no>=id ; last job to use this parameter
end else
* ================================================================
* controling field is <1>
* ================================================================
   ins new.param before params<1, mv.no>
* ================================================================
* dependant fields 2 thru 6
* ================================================================
   ins new.default before params<2, mv.no> ; * first default.value
   ins new.default before params<3, mv.no>
   ins 'N' before params<4, mv.no>
   ins id before params<5, mv.no> ; * first job to use this parameter
   ins id before params<6, mv.no>
end

================================================================
locating multivalued data by scanning each value
 field 1 cantains a string
================================================================
string.search='Src'
no.params=dcount(params<1>, @VM)
found=@false
for i=1 to no.params until found
   param=params<1,i>
   if index(param, search.string, 1) >0 then
      found=@true
   end
next i
if found then
   do something
end
================================================================
UVODBC
================================================================
Controlled by 3 files

1. $UVHOME/.odbc.ini
2. $UVACCOUNT/uvodbc.uvconfig
3. $INFORMIXDIR/etc/sqlfiles

You should not have to shutdown DataStage and restart it.
Try DS_CONNECT to test it or view data within a job.
================================================================

UVHOME=`cat /.uvhome/` ; # works on any UNIX box
UVACCOUNT="/bto/appl/uv/dsproj" ; # just an example
INFORMIXDIR="/bto/appl/informix" ; # just an example
================================================================

cd $UVHOME
vi .odbc.ini
================================================================
[ODBC Data Sources]
Oracle7=
Informix=
ORSAFD1=
ORSTAGD1=
ORSTAGP1=
ORNODSD1=
ORNODSP1=
ORSDDSP1=
ORDEVTST=
ORSAFT1=
BARNEYCCCODBC=
ORSAFUAT=
icsdm=

[Oracle7] Driver=/bto/appl/uv/../branded_odbc/lib/VMor713.so
Description=Oracle7
ServerName=oraclehost
LogonID=odbc01
Password=odbc01

[ORSAFD1]
Driver=/bto/appl/uv/../branded_odbc/lib/VMor815.so
Description=Oracle8
ServerName=ORSAFD1

[SNAPSHOTS]
QEWSD=36952
Driver=/bto/appl/uv/../branded_odbc/lib/VMinf15.so
database=snapshots
hostname=barney.bst.bls.com
Service=acac
Servername=icsdm
Description=Informix ODBC connection to Barney's snapshots database

[SNAPALT]
QEWSD=36952
Driver=/bto/appl/uv/../branded_odbc/lib/VMinf15.so
database=snapshots
hostname=90.14.152.43
Service=acac
Servername=icssnap
Description=Informix ODBC connection to E3500's snapshots database

================================================================
dev .odbc.ini looks like this
================================================================
[SNAPSHOTS]
QEWSD=36952
Driver=/bto/appl/uv/../branded_odbc/lib/VMinf15.so
database=snapshots
hostname=barney.bst.bls.com
Service=acac
Servername=icsdm
Description=Informix ODBC connection to Barney's snapshots database

[SNAPALT]
QEWSD=36952
Driver=/bto/appl/uv/../branded_odbc/lib/VMinf15.so
database=snapshots
hostname=90.14.152.43
Service=acac
Servername=icssnap
Description=Informix ODBC connection to E3500's snapshots database

================================================================

cd $UVACCOUNT
vi uvodbc.config
================================================================
[ODBC DATA SOURCES]
<localuv>
DBMSTYPE = UNIVERSE
network = TCP/IP
service = uvserver
host = localhost

<ORSAFD1>
DBMSTYPE = ODBC

<SNAPSHOTS>
DBMSTYPE = ODBC

<SNAPALT>
DBMSTYPE = ODBC
================================================================

cd $INFORMIXDIR/etc
vi sqlhosts
================================================================
icsdm ontlitcp 90.14.152.9 acac
# The following points to the "alternate BARNEY":
icssnap ontlitcp 90.14.152.43 acac
================================================================
DataStage exports and importing
================================================================
Need to Compile afterwards both jobs and routines.
================================================================

================================================================
SQL Against UV files
================================================================
Use ' not ".
Do not ' on numbers.
Use ; at the end of each line.
HELP SQL. Will show you syntax.
Multivalues are very difficult in SQL to update.
Store your sql programs in your BP.
Create remote pointers to run them.
SELECT * will not work without @SELECT.
INSERT may not work without @REVISE.

UV.VI KDBP COUNT.PROD.SQL
================================================================
PA
SELECT
   PROD_ID,
   COUNT(*)
FROM
   HaNodsSoProdIDs.DF
WHERE
   COMPLETION_DATE BETWEEN 20010401 AND 20010430
GROUP BY
   PROD_ID
ORDER BY
   PROD_ID
;


ED VOC SQL.COUNT.PROD
================================================================
R
KDBP
COUNT.PROD.SQL


SQL.COUNT.PROD          (to run program)
================================================================
PROD_ID    COUNT ( * )

    -99         182714
    -98         559953
     -1         185902
      1        1496174
      2             47
      3           2113
      4            635
      5          16632
      6            219
     10          15823
     31             61
     32          29189
     36        1067180
     37          79778
     40          51007
     41            223
     42          17877
     43           2224
     48           1527
     49              3
     51            129
     52           1329
     79            253
     85              3
     86            870
     91             14
     92             12
    100           1446
    101             36
    102            250
    110              4
    112              1
    141            605
    142             96
    155          51506
    156           2373
    157            455
    999           6316

38 records listed.
>

================================================================
Creating a hash file out of ODBC connection
This creates a Hash file called DS1 from STREAM_DEF
================================================================

PA
DELETE.FILE DS1
CONNECT ORSILOD1
DATA user
DATA password
DATA .U DS1
DATA SELECT * FROM STREAM_DEF WHERE BATCH_NUM LIKE '86%' AND BATCH_OPER = 'DSRUN';
DATA N
DATA .Q

================================================================
program to create distributed hash file
================================================================
      prog.id='CREATE.MULTI.PART.FILE'
      usage=prog.id:' filename no.of.parts dictname'
      prompt=''
      true=1
      false=0
      get(arg.)filename else
         print usage
         stop
      end
      get(arg.)no.of.parts else
         print usage
         stop
      end
      get(arg.)dictname else
         print usage
         stop
      end
      get(arg.)run.prog else run.prog=false
      if not(num(no.of.parts)) then
         print usage
         stop
      end
      if no.of.parts<1 or no.of.parts>10 then
         print 'No.of.parts too big or too small'
         stop
      end
      open 'VOC' to VOC else stop
      open 'DICT', filename to MASTER.FILE else
         print filename:' does not exist'
      end
      read master.rec from VOC, filename else stop
      if master.rec<1>[1,1] <> 'F' then
         print filename:' not a F-Pointer'
         stop
      end
      read dict.rec from MASTER.FILE, dictname else
         print 'No ':dictname:' in DICT, ':filename
         stop
      end
      df.file=filename:".DF"
      ecmd='DEFINE.DF ':df.file:' INTERNAL ALGORITHMS ':dictname
      print ecmd
      if run.prog then execute ecmd
      for i=1 to no.of.parts
         new.file=df.file:'.':i
         ecmd='CREATE.FILE DATA ':new.file:' DYNAMIC'
         print ecmd
         if run.prog then execute ecmd
         read voc.rec from VOC, new.file then
            voc.rec<3>=master.rec<3>
            if run.prog then write voc.rec on VOC, new.file
         end else
            print new.file:' not created'
            if run.prog then stop
         end
         ecmd='DEFINE.DF ':df.file:' ADDING ':new.file:' ':i:' INTERNAL ALGORITHMS ':dictname
         print ecmd
         if run.prog then execute ecmd
      next i
   end

================================================================