Datastage links
http://isecor.com/kb/documentation/orchadmin.html http://www.anotheritco.com/
Year: 2013
Clear &PH& directory
-bash-3.2$ cat ClearPH.ksh #!/bin/ksh ################################################################################ # File Name : ClearPH.ksh # Description : Clears the &PH& directories of any files older than 10 days. # This is a regular datastage maintenance task required as per: # http://www-01.ibm.com/support/docview.wss?uId=swg21414210 # # Change …
How to create UNLOCK statement in VOC
http://www.dsxchange.com/viewtopic.php?t=88975&postdays=0&postorder=asc&highlight=inode+unlock&start=15 1. Ask the person (login Id) having the issue to get out of DataStage all together… 2. In Administrator click on the Command button 3. Execute LIST.READU and note the ALL Inode and User No for the Login Id …
sqlplus – produce clean extract file
syntax sqlplus -S <userid>/<password>@<database> @<sql-file> > <output-file> 2> /dev/null example sqlplus -S <meuser>/<mepass>@<medatabase> @<mequery.sql> > <meoutput.dat> 2> /dev/null The below lines need to be at the top of the sql-file to ensure the output-file is formatted correctly. -S set …
sqlldr
syntax sqlldr <schema_name>/<schema_password>@<database_name> control=<control_file> log=<log_file> data=<data_file> example sqlldr <meschema>/<mepassword>@<medatabase> control=<mecontrol_file> log=<melog_file> data=<medata_file> <control_file>.ctl control script LOAD DATA APPEND INTO TABLE <TABLE_NAME> FIELDS TERMINATED BY ‘<FIELD_SEPARATOR_VALUE>’ OPTIONALLY ENCLOSED BY ‘<QUOTE_CHARACTER>’ TRAILING NULLCOLS (<COL1>, <COL2>, <COL3>)
Rename Table
syntax RENAME <old-table-name> TO <new-table-name> example RENAME cutomers; TO new_customers
Random sample of records
syntax SELECT <query-fields> FROM <table-name> sample(<%age of records to be returned>) [WHERE <condition>] example SELECT <customer-name> FROM <customer> sample(10)
Copy Table – more powerful
syntax COPY FROM <schema>/<password>@<database> TO <schema>/<password>@<database> CREATE | REPLACE | INSERT | APPEND <tablename> USING <Select clause that returns a results set> example COPY FROM one-schema/one-schemas-password@one-database TO another-schema/another-schemas-password@another-database> CREATE replica-table USING SELECT * FROM original-table Good idea to create …
Copy Table
syntax CREATE TABLE AS SELECT FROM example CREATE TABLE new_cusomer AS SELECT * FROM customer
Remove all double spaces from a file
sed ‘s/ */ /g’