Asset Suite / PassPortGeek

*********** NOTICE *******************

PRIOR TO 2010-08-26 THIS SQL HAD AN ERROR - "Sign OK" and "Null OK" indicators were swapped.

If you suspect you might have a problem, compare custom gencopy members against baseline gencopy members, i.e., compare the gencopy's from customized sym's and syr's against the gencopy's that were created when baseline PassPort or Asset Suite was installed. Many thanks to Greg for thinking this through.

-Webmaster 20100826

*********** NOTICE *******************

Create a file (dd.sql) with the following SQL. Works for Oracle and DB2. DB2 notes below.

set newp 0
set pau off
set hea off
set spa 0
set lin 610
set pages 0
set feed off
set termout off
set tab off
set array 0010
set longc 0080
set long 0080

select
' ' || ELEM_NAME || ELEM_NBR ||
SUBSTR(TO_CHAR(ELEM_LENGTH,'0000'),2,4) ||
SUBSTR(ELEM_TYPE,1,7) || CODE_QUALIFIER || ELEM_EDIT_TYPE ||
CODE_DESC_LNG || CODE_LOCKED_SW || VALIDATION_ROUTINE ||
PROMPT_NAME || MORE_DETAIL_NAME || LITERAL_VALUE ||
SUBSTR(TO_CHAR(DECIMAL_SCALE,'00'),2,2) ||
NULL_OK || SIGN_OK || ELEM_FORMAT || LAST_UPDATED_DATE ||
LAST_UPDATED_TIME || ' ' (6 spaces) || PASSPORT ||
OLE_META_CLASS || ELEM_DESC_NAME || FIXED_FORMAT ||
RTRIM(ELEM_DEFINITION,' ')
FROM TIDDDMST;

DB2 required changes.

Oracle: SUBSTR(TO_CHAR(ELEM_LENGTH,'0000',2,4)
DB2: SUBSTR(CHAR(ELEM_LENGTH),2,4)

Oracle: SUBSTR(TO_CHAR(DECIMAL_SCALE,'00'),2,2)
DB2: SUBSTR(CHAR(DECIMAL_SCALE),2,2)

Oracle: RTRIM(ELEM_DEFINITION,' ')
DB2: Etc, it’s in the manuals.

Run sqlplus userID/password@database < dd.sql > dd.txt The rerouting of standard output results in variable length records which are required for importing into a Data Dictionary within Analyst Workbench.

Edit dd.txt removing garbage records from the front and back.

FTP the file to the PC, example target file: c:\passport\PPv9dd\ppv9dd.txt

Enter Analyst Workbench, do not open a Data Dictionary. Create an empty Data Dictionary via: File, New, Dictionary, find the directory you want and type in a file at File Name, example: ppv9dd.dct (file name changed). This will open an empty Data Dictionary called ppv9dd.

File, Import, find your text file, ppvdd.txt, and select the text file. The text file should populate the Data Dictionary.