Datastage :Importing Table Definitions With OrchDbUtil

orchdbutil is a command line utility that allows you to print out table table definitions. It connects to the database,
queries the database catalog and prints the table definition to the console. The table definition is an orchestrate
definition - i.e. it uses internal data types rather than SQL data types when defining columns.
The following example shows how orchdbutil is used.

$ orchdbutil show MSF020 -dbtype oracle -server orcl -dboptions {user=jim,password=jim}
##I IIS-DSEE-TFCN-00001 21:27:30(000) 
IBM WebSphere DataStage Enterprise Edition 8.5.0.5746
Copyright (c) 2001, 2005-2008 IBM Corporation. All rights reserved

##I IIS-DSEE-TDOR-00303 21:27:31(001)  Oracle session NLS parameters: CODESET=.1252  NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252" NLS_LANGUA
GE=AMERICAN NLS_TERRITORY=AMERICA NLS_CURRENCY=$ NLS_ISO_CURRENCY=AMERICA NLS_NUMERIC_CHARACTERS=., NLS_CALENDAR=GREGORIAN NLS_DATE_FORMAT=DD-MON-RR N
LS_DATE_LANGUAGE=AMERICAN NLS_CHARACTERSET=AL32UTF8 NLS_SORT=BINARY NLS_TIME_FORMAT=HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT=DD-MON-RR HH.MI.SSXFF AM NLS_T
IME_TZ_FORMAT=HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT=DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY=$ NLS_NCHAR_CHARACTERSET=AL16UTF16 NLS_COMP=BI
NARY NLS_LENGTH_SEMANTICS=BYTE NLS_NCHAR_CONV_EXCP=FALSE.
##I IIS-DSEE-TCDU-00009 21:27:31(002)  Database connect options: dbname= , server='orcl', dboptions='{user=busi_ldg,passwor
d=busi_ldg}'.
##I IIS-DSEE-TCDB-00002 21:27:31(003)  Database connect options: dbname= , server='orcl', dboptions='{user=busi_ldg,passwor
d=busi_ldg}'Library orchoracle is loaded.
##I IIS-DSEE-TCDU-00001 21:27:31(004)  record
( ENTRY_TYPE: string[1];
  ENTITY: string[10];
  DSTRCT_CODE: string[4];
  DEFAULT_FLAG: string[1];
  PASS_WORD: string[10];
  DEFAULT_MENU: string[10];
  SECURITY_ACCESS: string[1];
  LAST_ACC_DATE: string[8];
  PSW_UPD_DATE: string[8];
  LAST_MOD_DATE: string[8];
  LAST_MOD_TIME: string[6];
  LAST_MOD_USER: string[10];
  CREATION_DATE: string[8];
  GLOBAL_PROFILE: string[10];
  EMPLOYEE_ID: string[10];
  PROFILE: string[250];
  OWNER_ID: string[10];
  OGUI_GLOBAL_PROF: string[10];
  LAST_NO_ERR_LOGS: string[2];
  PROFILE_LOGIN_LCKD: string[1];
  RECENT_PWD_GRP: string[204];
)
$

Unlike the Designer interface, the output is fairly raw. However it provides us with the necessary information to begin our scripting process.

We will use Perl to call orchdbutil, capture and parse its output and then convert the output to DSX format. The script will contain a configuration section that defines database default connection details and other information necessary to build the DSX file. It will accept a number of command line options that change the default values. One of the options is a list of tables. The script will process every table in the list producing a DSX file for each one. These DSX files can then be concatenated into a single DSX and imported using the dscmdimport utility.
Here's how it works.

$ ll
total 24
-rwxrwxrwa   1 Administrators  None              11429 Apr 16 07:07 importTableDefs.pl
-rwxrwxrwa   1 Administrators  None                 16 Apr 16 07:08 tables.lst
$ cat tables.lst
MSF020
MSF034
$ ./importTableDefs.pl -tableList allTables.txt
Exporting table definition for MSF020
Exporting table definition for MSF034
$ cat *.dsx > allTables.dsx
$ dscmdimport /D=kuatro:9080 /U=jim /P=jim /H=kuatro /NUA EPM ./allTables.dsx
Attaching to 'EPM' ...
Importing './allTables.dsx' to project 'EPM' ...
DSImport started at: 07:15:01 2011-04-16

Importing Table definitions
.
Importing Table definitions
.DSImport ended at: 07:15:02 2011-04-16

$

As you can see, we are now able to import dozens of tables very quickly and easily.

Scripting the import process provides a quick and painless way of importing table definitions for any number of tables and, if required, to refresh these on a regular basis. This saves Developers from having to manually import and they can also be confident that they are working with the latest table definitions. Furthermore, the table definition DSX files can be stored in a Change Repository such as subversion. This will keep track of when and what changes where found.
The full listing is in the attached file.