Find table/view name & ddl from sql copybook

This works for PS cobol copybooks, assuming its formatting style, text sacn/grab change needed if not the same format.

1. get rec name: 

cut -c7- xxx.sql|  grep -v ^\* | sed  's/\-\-.*$//' | sed -n '/FROM/I,/WHERE/Ip'  | grep -iv WHERE | awk -F"[(), ]" 'BEGIN{IGNORECASE=1} {for(i = 1; i <= NF; i++) if (match ($i,"^PS")) print $i}'|sort|uniq

2. get ddl:

define obj=&1

declare 
   buf clob;
   typ varchar2(30);

begin 


   dbms_output.ENABLE(200000);

   select OBJECT_TYPE into typ from user_objects where object_name = upper('&obj') and OBJECT_TYPE in ('TABLE','VIEW');   

   select DBMS_METADATA.GET_DDL(typ,upper('&obj')) into buf from dual;
   

   DBMS_OUTPUT.PUT_LINE(buf);

 exception
         WHEN OTHERS then
           declare
              errcd  NUMBER := SQLCODE;
              errmsg VARCHAR2(300) := SQLERRM;
           begin
              DBMS_OUTPUT.PUT_LINE(errcd ||','||errmsg);
           end;

end;
/

Tivoli conman commands

 

  . conman (command console)

·       rr  WKST#JOB_STREAM.JOB (run a job)

·       sj (look at all job streams & jobs status)

         sj JOBSTREAM (look at a job stream)

·       sj @#@.@+state=EXEC (look at all running jobs)

sj @#@.JOB (look at job, scheduled/run/complete)

f WKST; 50 (change job fence on WKST to 50)
f WKST; go (prevent all jobs on WKST from running)

more: 

https://www.ibm.com/support/knowledgecenter/SSRULV_9.2.0/com.ibm.tivoli.itws.doc_9.2/distr/src_ref/awsrgconmancmds.htm

AE Analyzer - trace file parser

Peoplesoft AE is the worst application platform I have ever encountered in 30 years of  programming, with last 20 as a PS technical consultant.  It's purely made for revenue purpose (training/certification/consulting) at the expense of coding efficiency and performance. It's a hell when it comes to debugging and analyzing AE "applications". 

You can do every single advertised "strength" of AE in a ton of other languages, except for those "benefits" that are PS proprietary so they can not easily be achieved otherwise, such as "auto" objects update or calling peoplecode snippets.

The result is a bloated, inefficient, debugging & performance nightmare, ugly-ware.  

That said as a consultant I do have to work AE "applications", and it's an academic exercise trying to sort through the trace files. 

Generally there are 2 options to deal with AE: 

• entire app analysis - nightmarish task; code is broken into pieces and flow is controlled by a dropdown list of actions; no simple, easy to understand if-then-else conditional logic, replaced with awkward 'and EXISTS (...)'. 

• runtime abend debugging - is error on a sql or peoplecode? 

1. peoplecode -

1. do not clear AE, rerun to create AET & trc
2. grep '^\-\- .* \.*(' XXX.AET, this creates indented outline of steps up to abend
3. check "Iteration n of ..." for cursor 
4. cross ref (using DTTM value) back to trc to see the variables and logic of abend

2. sql - 


Here are some steps that make the job easier.

AE Trace:
Flag: -TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 121

1. grep ^"\-\- [0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\}.[0-9]\{3\} [\.I]" AE_nnnnnn.AET | sed -e 's/^\.* .* \([\.I].*\)$/\1/'  >xxx

2. sed -e 's/\.(PO_POCALC\.\(.*\)) \(.*\)$/\1 \2/' xxx>yyy

2a. sed -e 's/.(\([A-Z].*\)) /\1 /' xxx | sed ':1; s/^\([^[a-zA-Z(]*\)\./\1_/;t1;s/^_/|_/' | m  (no hiding AE prog name)

3. sed ':1; s/^\([^[a-zA-Z(]*\)\./\1_/;t1; s/^MAIN\.//;s/^_/|_/' yyy | m


Count # of sqls in AET:

 find . -name *AET -exec echo {} \; -exec ksh -c  "grep \(SQL\) {} |wc -l" \; -exec ksh -c "grep \(SQL\) {} | cut -d' ' -f2 | wc -l" \;



-----------------------------------------------------------------------------------
# include "Iteration of"
sed -n 's/\(^\-\- [0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\}.[0-9]\{3\}\) \([\.I].*\)/\2/p' XXX.AET| grep -v ^Instance > aaa

# switch 1st 2 items so "Iteration ..of"  is lined up with section name
sed 's/\(^Iteration.*of \)\(\.\.*\)\((.*\)/\2\1\3/' 

# remove 1st set of () 

sed  's/\(^\.\+\)(\(.*\)) \(.*\)/\1\2 \3/' aaa

# putting all together
sed 's/\(^Iteration.*of \)\(\.\.*\)\((.*\)/\2\1\3/'  aaa| sed  's/\(^\.\+\)(\(.*\)) \(.*\)/\1\2 \3/'|sed 's/^\.\(.*\) /\1 /'| sed ':1; s/^\([^[a-zA-Z(]*\)\./\1_/;t1;s/^_/|_/' | m 

# new
sed -n 's/^\-\- \([0-9]\{2\}:[0-9]\{2\}:[0-9]\{2\}.[0-9]\{3\}\) \([\.I].*\)/\2 \1/p' AE_FS_BP_2740810_1121153646.AET | grep -Ev "(^Instance|Tracing)"   | sed 's/\(^Iteration.*of \)\(\.\.*\)\((.*\)/\2 \1\3/'  | sed  's/\(^\.\+\)(\(.*\)) \((.*)\) /\1 \2 \3 /' | sed 's/^\.\(.*\) /\1 /' | sed ':1; s/^\([^[a-zA-Z(]*\)\./\1_/;t1;s/^_/|_/'| m

------------------------------------------------------------------------------------

This is the result, cross reference between 3 frames: AET source, AET flow, Peoplecode:

-----

Living up to its inferior product status even PS' own AET Analyzer does not work on "large" (140MB) trc file:

find folder older than n days and remove

• find folder older than 30 days and remove

## display folders
find . -type d -ctime +30 -exec echo -ne "folder:" {} "\t " \; -exec stat --format=%y {}   \; | sort -k 3,3

## remove folders
find . -type d -ctime +30 -exec rm -r {} \;