Rough

SELECT tables.stats_time as statstime

,trim(trailing from substr(tables.tabschema,1,60))||'.'|| trim(trailing from substr(tables.tabname,1,60)) as tabname

, card as rows_per_table

, decimal(float(tables.npages)/ ( 1024 / (tbspace.pagesize/1024)),9,2) as used_mb

, decimal(float(tables.fpages)/ ( 1024 / (tbspace.pagesize/1024)),9,2) as allocated_mb

FROM syscat.tables tables

, syscat.tablespaces tbspace

WHERE tables.tbspace=tbspace.tbspace

MF code to FTP a File.

//DDS1764J JOB NOTIFY=DDS1764// STEP001 EXEC PGM=FTP,PARM=’ (EXIT’ //SYSPRINT DD SYSOUT=*// OUTPUT DD SYSOUT=* //INPUT DD *// // // // // //DIR // //PUT ‘PUT ‘PUT ‘QUIT //

//MF Code to ZIP a file.//


 * THIS JOB WILL ZIP A MAINFRAME FLAT FILE

//ZIP01 EXEC PGM=PKZIP,REGION=2048K// STEPLIB DD DISP=SHR,DSN=PKZIP.LOAD //INFILE DD DSN=GDG.JCL,DISP=SHR// SYSPRINT DD SYSOUT=* <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;">//SYSOUT DD SYSOUT=*// SYSIN DD * <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;"> -ECHO <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;"> -MEMORY_MODE(SMALL) <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;"> -ARCHIVE(GDG.ZIP) <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;"> -DATA_TYPE(BINARY) <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;"> -METHOD(MAXIMUM) <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;"> -INDD(INFILE) <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;">/* <span style="background-color: #000000; color: #00ff00; font-family: Courier,'Courier New',sans-serif; font-size: 11px;">//*//

//13th Sept 2013//

//TEMP_DIR=$HOME/scripts/// //Script to get DSJOB run Stats.//

//dsjob -ljobs <Project> > "$TEMP_DIR"Jobs.txt// //rm "$TEMP_DIR"Jobs_Report.txt//

//for Job_Name in `cat "$TEMP_DIR"Jobs.txt`//

//#for Job_Name in `cat "$TEMP_DIR"Jobs.txt`//

//do//

//#dsjob -jobinfo <Project> $Job_Name >> "$TEMP_DIR"Jobs_status.txt//

//dsjob -report <Project> $Job_Name >> "$TEMP_DIR"Jobs_Report.txt//

//done//

//sed 's/// g' "$TEMP_DIR"Jobs_Report.txt > "$TEMP_DIR"temp.txt

egrep 'JOB|start|end|status|elapsed' "$TEMP_DIR"temp.txt > "$TEMP_DIR"temp_1.txt

sed '1~5 {N;N;N;N;s/\n/|/g}' "$TEMP_DIR"temp_1.txt > "$TEMP_DIR"temp_2.txt

sed 's/STATUS REPORT FOR JOB: //g' "$TEMP_DIR"temp_2.txt > "$TEMP_DIR"temp_3.txt//

//sed 's/ Job start time=//g' "$TEMP_DIR"temp_3.txt > "$TEMP_DIR"temp_4.txt

sed 's/ Job end time=//g' "$TEMP_DIR"temp_4.txt > "$TEMP_DIR"temp_0.txt//

//sed 's/ Job status=//g' "$TEMP_DIR"temp_0.txt > "$TEMP_DIR"temp_5.txt

sed 's/ Job elapsed time=//g' "$TEMP_DIR"temp_5.txt > "$TEMP_DIR"temp_6.txt//

//mv "$TEMP_DIR"temp_6.txt "$TEMP_DIR"Job_report.txt//

//rm "$TEMP_DIR"temp_4.txt "$TEMP_DIR"temp_3.txt "$TEMP_DIR"temp_2.txt "$TEMP_DIR"temp_1.txt "$TEMP_DIR"temp.txt "$TEMP_DIR"temp_0.txt//

//5th July 2013//

//Unix xml Utility to know the value of a tag element.//

//Check if the xml is a valid xml or not.// //<span style="color: #222222; font-family: Arial,sans-serif; font-size: 10pt;">xmllint --noout $XMLFOLDER/$XMLFILE 2>&1 >/dev/null //

//Check xml Values in a tab.// //xmllint --shell File_Name.xml <<< "cat// InsuredLName/text" | grep -v "^/ >"

Tip: Some time the DataStage window will be hiding on the client(This has be observer in cases where there are dual screens). In such case where you don't see the screen opening on double click.

ALT + TAB Select the blank screen Press ALT SPACES bar then Max _

__TEMP_DIR=/temp__ __ISTOOL_DIR=/opt/IBM/InformationServer/v81/Clients/istools/cli__ __#PROJECTS=/extract/data/etldata/projects.txt__ __PROJECT_NAME=test__ __BACKUP_DIR=/extract/data/etldata__ __BACKUP_FILES=8__ __DOMAIN_NAME=bi-etl-a:9080__ __HOST_NAME=bi-etl-a__ __USER=user__ __PASSWORD=pass__ __DATE=`date '+%b_%e_%Y'`__ __for PROJECT_NAME in `cat /extract/data/etldata/projects.txt`__ __do__ __echo " " >> /extract/data/etldata/backup_project_log.log__ __echo "${PROJECT_NAME}" >> /extract/data/etldata/backup_project_log.log__ __echo "-" >> /extract/data/etldata/backup_project_log.log__ __echo "Log for "\"$PROJECT_NAME\"" Start's @ `date` " >> /extract/data/etldata/backup_project_log.log__ __echo "-" >> /extract/data/etldata/backup_project_log.log__ __BASE=${HOST_NAME}/${PROJECT_NAME}__ __${ISTOOL_DIR}/istool export -domain ${DOMAIN_NAME} -u dsadm -p ${PASSWORD} -ar ${BACKUP_DIR}/${PROJECT_NAME}_${DATE}.isx -ds "-incdep -base=$BASE Jobs/*/*.*" >> /extract/data/etldata/backup_project_log.log__ __echo "---" >> /extract/data/etldata/backup_project_log.log__ __echo "Log for "\"$PROJECT_NAME\"" End's @ `date` " >> /extract/data/etldata/backup_project_log.log__ __echo "---" >> /extract/data/etldata/backup_project_log.log__ __echo " " >> /extract/data/etldata/backup_project_log.log__ __done__
 * Export Data Stage Jobs**

__ftp -v -n "q-w-v" << ss__ __user "ftpusr" "pass"__ __cd /extract/data/scfdata__ __delete cignadwTesting.txt__ __delete WebvmlTesting.txt__ __quit__ __ss__
 * Automated FTP Process**

__**To remove leading and trailing spaces use the command**__

__sed 's/ *__//__g' Oracle_denormalised.dat > Oracle_DNSrespace.dat__//

__**To know if we have any special characters we need to use this command.**__

cat -v

//__**To remove the Control M character when moving the file from Dos to Unix**__//

//__sed 's/ctrl+v+ctrl+m/space__//__/g' infile > outfile__

sed 's/// g' Infile > outfile// = **__Line-terminating characters in integrated file system files__** = The AIX® and i5/OS® operating systems use different line-terminating characters in text files; for example, in files and shell scripts. The AIX applications that are the source for your i5/OS PASE programs expect that lines (for example, in files and shell scripts) will end with a line feed (LF). However, PC software and typical i5/OS software often ends lines with a carriage return and line feed (CRLF). code awk '{ gsub( /\r$/, "" ); print $0 }' newfile code

CRLF used with FTP
One example of where this difference can cause problems is when you use File Transfer Protocol (FTP) to transfer source files and shell scripts from the AIX operating system to the i5/OS operating system. The FTP standard calls for data sent in text mode to use carriage return and line feed (CRLF) at the end of a line. On AIX, the FTP utility strips the carriage return (CR) when it processes an inbound file in text mode. i5/OS FTP always writes exactly what is presented in the data stream and always retains CRLF for text mode, which causes problems with the i5/OS PASE run time and utilities. Where possible, use binary mode transfer from an AIX operating system to avoid this problem. Text files transferred from personal computers will, in most cases, have CRLF delimiting lines in the file. Transferring the files first to AIX will correct the problem. The following command can be used as a means to remove the CR from files in the current directory:code awk '{ gsub( /\r$/, "" ); print $0 }' newfile code

CRLF used with i5/OS and PC editors
You can also experience problems when you edit your files or shell scripts with editors on your system or with editors on your workstation (such as Windows® Notepad editor). These editors use CRLF as a new line separator, and not the LF that i5/OS PASE expects. Numerous editors are available (for instance, the editor) that do not use CRLF as new line separators.

__**FTP from the script with out using user name and password**__ _ ftp bsciw33i < binary get ${TAPEDIR}/${FILE} ${FILE} bye XX _

__**To convert the tab delimited file into Pipe delimited.**__

__sed 's/Tab and Cntrl+V/|/g'__ __sed 's/ /|/g'__

__**Running the SQL from the script and capturing the actual return codes**__ __fnRunSql __ __{__ __OUTPUT=''__ __SQLCODE=-99__ __OUTPUT=$(db2 +o connect && IFS=''; printf "${SQLSTRING}\n" 2>&1 | db2 -stec +pc ; db2 +o commit ; db2 +o connect reset)__ __#OUTPUT=$(IFS=''; printf "${SQLSTRING}\n" 2>&1 | db2 -stec +pc && db2 +o terminate)__ __printf "${OUTPUT}\n" | wc -c | read OUTPUT_BYTES__ __printf "${OUTPUT}\n" | grep -i -f ${WRAPPER}/error_keyword_list | wc -l | read OUTPUT_ERRORS__ __if (( OUTPUT_BYTES <= 2 )) then__ __return 1__ __elif (( OUTPUT_ERRORS )) then__ __return 2__ __fi__ __SQLCODE=$(printf "${OUTPUT}\n" | tail -1)__

__**DATE LOGID IN DB2**__

__t.CDATE_DT_KEY as CDATE_DT_KEY__ __, t.CDATE_${DATETYPE} as CDATE_DT__ __, smallint(month(date(t.CDATE_${DATETYPE}))) as CDATE_DT_MTH__ __, integer(udf.trim(ltrim(rtrim((char(year(date(t.CDATE_${DATETYPE})))))__ __||ltrim(rtrim(substr(char(date(t.CDATE_${DATETYPE})),1,2)))))) as CDATE_DT_YR_MTH__ __, substr(char(quarter(date(t.CDATE_${DATETYPE}))),1,2) as CDATE_DT_QTR__ __, substr(UDF.TRIM(ltrim(rtrim(substr(char(t.CDATE_${DATETYPE}),7,4)))|| 'Q'|| char(quarter(date(t.CDATE_${DATETYPE})))),1,6) as CDATE_DT_YR_QTR__ __, year(date(t.CDATE_${DATETYPE})) as CDATE_DT_YR__ __, smallint(UDF.TRIM(char(day(date(t.CDATE_${DATETYPE}))))) as CDATE_DT_DAY__ __, UDF.TRIM(substr(char(date(t.CDATE_${DATETYPE})),4,2)||' '|| char(substr(ucase(monthname(date(t.CDATE_${DATETYPE}))),1,3))||' ' || char(year(date(t.CDATE_${DATETYPE})))) as CDATE_DT_TEXT__ __, '${LOAD_DATETIME}' as LOAD_TIMESTAMP__ __from ${SCHEMAS}.STAGE_HST_CLMDATE_DIM_UNQ_${DIM_PROCESS_YYYYMM}__

__awk -F'|' '{print $35}'__


 * 07/20/2009 Portland Oregon**

__awk__
__To use [|awk] to convert a Windows file to Unix, at the Unix prompt, enter:__

__awk '{ sub("\r$", ""); print }' winfile.txt > unixfile.txt__

__To convert a Unix file to Windows using awk, at the command line, enter:__ __awk 'sub("$", "\r")' unixfile.txt > winfile.txt__

__On some systems, the version of awk may be old and not include the function sub. If so, try the same command, but with gawk or nawk replacing awk.__

__awk '{print length;exit}'__
 * Print the Length of a record.**


 * Convert a variable length record to a fixed length records of a fixed size.**

__awk '{printf "%-762s\n", $0}'__


 * While sending a file from Unix to MF this is an useful command.__**

ftp:Path\file.txt' Open command =quote site lrecl=500 recfm=fb

24Feb2011

To count the number of records from .gz file use zcat fielname.gz | wc -l

Flag=0 if -s /di/Projects/game/scripts/Rej_list.txt then rm /di/Projects/game/scripts/Rej_list.txt fi for File_Name in `cat /di/data/game/outbound/File_names.txt` do Var1=`awk 'END {print NR}' /di/data/game/outbound/$File_Name` if $Var1 -gt 0 then Flag=1 echo $File_Name >> /di/Projects/game/scripts/Rej_list.txt fi done
 * 6th June 2011..... Pune : Netezza runs.**
 * 1) l!/bin/ksh

if $Flag -eq 1 then cat /di/Projects/game/scripts/Rej_list.txt| mutt -s "List of reject files having records" karthik.medasani@gmail.com fi

Flag=0 rm /di/Projects/game/scripts/test_delme.txt for Table_Name in `cat /di/Projects/game/scripts/Table_names.txt` do Var1=`$DI_UTILS_DIR/bin/run_sql.ksh -pf nz -u $NZ_USR -host $NZ_SRVR -d $NZ_ALEX_STG_DB_NM -sql "SELECT count(*) as Table_Name from $NZ_ALEX_STG_DB_NM..$Table_Name " |sed "s/ //g" | grep "^[0-9]" ` if $Var1 -eq 0 then Flag=1 echo $Table_Name >> /di/Projects/game/scripts/test_delme.txt fi done
 * 1) !/bin/ksh

if $Flag -eq 1 then

cat /di/Projects/game/scripts/test_delme.txt| mutt -s "List of dimension tables having zero records" karthik.medasani@gmail.com fi