TableJobs Benefits to Programmers & Analysts

TableJobs are invaluable to IT professionals at any Unix/Linux/Windows site because:

  1. TableJobs build summary tables of counts & values to be dumped to a report file at EOF. There are various TableJobs for various file-types (text,fixed,variable), but we think you will find 'table3d' for text files most useful to supply you with statistics from directories of programs, scripts, parameter files, etc.

  2. You specify a Key-Word preceding the Target-Word to be table summarized. For example, given a directory of JCLs, you could specify KeyWord "PGM" preceding the program name in statements such as "// EXEC PGM=programxx".

  3. For FTP scripts, you could table the IP#s following the KeyWord "OPEN", or table the userids & passwords following the KeyWord "USER". For SQL scripts you could table the table-name following the KeyWord "FROM".

  4. You can also create table summary reports from fixed or variable length data-files which may have packed/binary fields (which can be tabled).

  5. You can use Tablejobs to research the impacts of planned changes to your scripts & programs.

  6. TableJobs create reports in subdir stats/... of the current working directory, automatically named from the file or directory concatenated with Keywords used. This allows you to review table history & easily modify & repeat.

  7. The table reports include all relevant info (files,dirs,Keywords,date/time, user,host,etc) to help you document problems & plan proposed changes.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

TableJobs - Contents


4A1. table3d - create table summary counts of desired items in text files
- pre-programmed job using uvcopy (powerful data manipulation utility)
- scan all files in directory building table statistics to dump at EOF
- table3d example#1 - counts of IP#s in FTP scripts

4B1. Check-List - Ready to run TableJobs test/demos ?
- Vancouver Utilities installed, Unix userid setup,
- setup your user account & profile to run TableJob demos
- OR setup subdir $HOME/demo in your existing account

4B2. Vancouver Utility files in $UV=/home/uvadm/...
- highlighting subdirs/files relevant to TableJobs

4B3. Files relevant to TableJobs already isolated in /home/uvadm/demo/...
- copy $UV/demo/* subdirs/files to your $HOME/demo/...

4C1. table3d user options --> uop=a0b3c0d0f0j0l0m0p1s0t0x0w1 <-- defaults
Many options allow you to define the items to be tabled compensating
for complex situations in your data files.

4D1. Most useful options - option 'w#'
Option w specifies the word# offset of the TargetWord from the KeyWord.
It defaults to 'w1' meaning the TargetWord to be tabled & counted is
the 1st word following the search KeyWord specified by arg1=...
Demo table3d with option w2 to summarize 2nd filenames on FTP puts

4D2. Most useful options - option 'y#'
Option 'y#' specifies the number of keywords to be tabled (default 1).
Demo table3d option y2 to summarize both userid & passwords following 'user'

4E1. Test files to demo table3d
- JCLs, FTP scripts, SQL scripts,

4E2. Table Summarize program names in JCL
Key-Word PGM, Target-Word program-name following PGM=...

4F1. Writing specific uvcopy jobs vs Pre-Programmed TableJobs

4F2. tableIP - custom written table summary demo (only 14 instructions)
- creates same table summary as table3d
- but only for 1 specific summary (vs general purpose table3d)
4F3.  Notes re uvcopy table summarize demo job 'tableIP'

4G1. Creating table summaries of SQL TABLEs in SQL scripts
Key-Word FROM, Target-Word table-name following FROM ...
4G2.  SQL scripts listed - test files for table3d demos

4G3. summarize SQL TABLEs in SELECT ... FROM TableName - 1st attempt
- result will be missing some TableNames
4G4.  Problem if Target-Word (table-name) not on same line as Key_Word (FROM)
 Could use utility 'combine1d' to combine SQL statements prior to table3d
4G5.  Problem Solution - SELECT stmnts combined onto 1 line
- by utility job 'combine1d'
4G6.  Rerun table3d inputting SELECT stmnts combined onto 1 line
- to create correct summary of SQL TABLEs

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

TableJobs - Contents (continued)


4H1. 'table2' - table summaries by fixed position fields displacement(length)
- versus table3 searc for Key-Words & table following Target-Words
- Demos using demo file of US Government Representatives & Senators
- downloaded Excell file, extracted csv file,& converted to fixed layout

4H2. table2 - Notes & Benefits

4H3. table2 demo Execution & Console-Log
- illustrated console-log prompts for options, arguments, table fieldnames,
  & prompt to view output report

4I1. 4 Table Summaries from dat1/UScongress
You can run various table summaries from the dat1/UScongress demo file.
We show a few of the shorter reports, followed by script sf/UScongress_stats,
which runs 13 reports that we thought were interesting.
First report - table US Representatives/Senators by Party

4I2. table US Representatives/Senators by House & Party

4I3. table US Representatives/Senators by Party & Sex

4I4. table US Representatives/Senators by State & Party

4J1. script to run multiple stats on dat1/UScongress Representatives & Senators

4K1. Table2 Summaries of Canadian Members of Parliament dat1/CanadaMPs
- samples of the 335 records with a column scale to determine field locations.

4K2. 3 Table Summaries run from dat1/CanadaMPs
1st CagovMPs table by Party

4K3. table CA MPs by House & Party
table CA MPs by Year-Elected

4K4. script sf/CanadaMPs to run multiple stats on dat1/CanadaMPs

Note - TableJobs also in UVdemos

TableJobs.htm is the same as UVdemos.htm#Part_4. TableJobs was duplicated as a separate document because it is 1 of the most useful parts of UVdemos.

SelectJobs.htm (same as UVdemos.htm#Part_3) is also a separate document.


UVscripts.htm (same as UVdemos.htm#Part_5) is also a separate document.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4A1. TableJobs - create Table Summaries via Keywords/Targetwords

TableJobs build summary tables of counts & values to be dumped to a report file at EOF. There are various TableJobs for various file-types (text,fixed,variable), but we think you will find 'table3d' most useful to quickly supply you with statistics from directories of programs, scripts, parameter files, etc.

For example, 'table3d' can read all files in the ftps/ directory, searching for KeyWord "open" & count occurrences of the following TargetWord (the IP#).

Example#1 - table summarize all IP#s in FTP scripts


 uvcopy table3d,fild1=sqls,arg1=open
 ===================================
  - read ftps/* search for "open" & count occurrences of following IP#
  - output report created in subdir stats/... & automatically named by concatenating
    directory + keywords separated by underscores

table3d report created in stats/...

 # /home/uvadm/demo/stats/ftps_open  <-- This report created by uvcopy:
 # uvcopy table3d,fild1=ftps,arg1=open
 # =====================================================================
 # - scan all files in directory for a keyword & table counts of following word
 # InDir=ftps  Keyword=open  Qual1=   Qual2=
 # WordSepsBlank=  Options=q1a0b3c0d0f0j0l0m0p1s0t0x0w1
 # - default selects following word, use option w2 to table 2nd word, etc
 # - specify arg1=Keyword,arg2=Qualifier-present,arg2=Qualifier-absent
 # &\,$=/>-<:(.');_*[|]"<--- arg4=punctuation to blank for word separation
 # abcdefghjklpqrsuALPRQ<--- equivalents, easier to enter, may enter "all"
 # Date=2019/06/17_12:18:57, Site=UV_Software, Host=uvsoft5, User=uvadm
 #===============================================================================
 #---> uvcopy table3d,fild1=ftps,arg1=open
 table3d  2019/06/17_12:19:01  Counts by Targetword following specified Keyword
 tbl#0001  tblt1f7 e0(48)         argument
 line#   1strec#  %      count  target-word
     1         2  25         1   192.168.0.4
     2         2  25         1   192.168.0.5
     3         2  50         2   192.168.0.6
                 100         4*  *TOTAL*

Notes, Benefits of table3d reports

  1. Output reports are automatically created in subdir stats/... named by concatenating the input directory & keywords separated by '_' underscores. The filename is coded on the 1st line of the report.

  2. The uvcopy command used to create the report is coded on the 2nd line, so you can look back at previous runs & repeat them (with modifications as desired).

  3. The reports are date/time stamped with all relevent info (site,host,user,options).

  4. See other examples in the tutorials ahead - userids & passwords in FTP ftps, programs called by all JCLs, table-names used by all SQL scripts, etc.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4B1. Vancouver Utility Programming Aids - TableJobs

Setup check-list - Ready to run TableJob test/demos ?

We will present a 'setup check-list' here, since you may be reading this in the separate TableJobs.doc vs UVdemos.doc which includes TableJobs as Part4.

See UVdemos.htm for the complete setup procedures, but here is a check-list of the steps required before you can execute these test/demos.

  1. Install Vancouver Utilities. See complete documenation at install.htm. Also see the install summary at UVdemos.htm#1A3.

  2. Setup your user login account if not already done. See UVdemos.htm#1C1. Setup your user profile (.bash_profile or .profile for ksh). User profile in homedir calling common_profile in /home/appsadm/env/...

  3. Setup subdir demo/ in your homedir & copy $UV/demo/* to your $HOME/demo/... See instructions on page '4B3'

  4. Here we list only the datafiles from $UV/dat1/... relevant to TableJobs You can see all the demo/dat1/... files at UVdemos.doc#1C2.

    testdata file contents

  5. Here in TableJobs, we will usually show just a sample of the relevant testdata files before each test/demo execution. See UVdemos.htm#1D1 - 1D6 to see all files & all contents of the testdatafiles.

  6. The next page '4B2' will show files in $UV (usually /home/uvadm) that are relevant to TableJobs.

  7. Page '4B3' gives instructions to copy TableJob testfiles to your $HOME/demo/... (shown here as: /home/userxx/demo/...)

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4B2. Vancouver Utility Programming Aids - TableJobs

Vancouver Utilities - subdirs relevant to TableJobs

Here is the Vancouver Utilities homedir showing only the most relevant subdirs required for the TableJob demos documented in this section of UVdemos.htm.

You could run the demos logged in as 'uvadm', but if you are sharing the system with other uvadm users, it would be better to copy the demo/subdirs/files to your own homedir. See the copy instructions below & an expanded view of the demo/... files on the next page.

 /home/uvadm             <-- $UV
 :-----bin                 - UV binaries (uvcopy,uvsort,uvcp,uvhd,uvlist,etc)
 :-----dat1                - test data files to demo various Vancouver Utilities
 :     :-----...         <-- 200+ testdata files for all uvcopy job demos
 :     :                   - files for TableJobs already copied to demo/dat1/...
 :     :                   - see next page --->
 :-----doc                 - Vancouver Utilities documentation (text)
 :     :-----UVdemos.doc   - this documentation (1 of 150 files)
 :     :----TableJobs.doc  - Part4 of UVdemos duplicated as a separate document
 :-----dochtml             - documentation in HTML (uploaded to www.uvsoftware.ca)
 :-***-demo              <-- demo files for TestDemo tutorials, copy to your homedir -->
 :     :                   - copy /home/uvadm/demo/* to your /home/userxx/demo/...
 :     :-----dat1          - data files for TableJob demos, see details on next page
 :     :-----jcl2          - mainframe JCL samples (8)
 :     :-----ftps         - SYSIN control cards for various purposes
 :     :-----sqls          - SQL scripts for TableJob demos, see next page -->
 :-----pf                <-- Parameter Files for uvcopy
 :     :-----util            uvcopy TableJobs
 :     :     :-----table1    - table field# in a delimited file
 :     :     :-----table2    - table fixed position field by dsplcmnt(length)
 :     :     :-----table3    - table Target-Word following specified Key-Word for 1 file
 :     :     :-----table3d   - table Target-Word following specified Key-Word
 :     :     :-----            for ALL files in a Directory
 :     :-----adm             - uvcopy jobs for uvadm admin
 :     :-----demo            - uvcopy jobs for various demos
 :     :-----IBM             - uvcopy jobs for Mainframe conversions
 :-----sf                <-- Script Files (bash or Korn shell)
 :     :-----adm             - scripts for uvadm admin
 :     :-----demo            - demo scripts
 :     :-----util            - utility scripts
 :     :-----IBM             - scripts for mainframe conversions
 :-----src               <-- Vancouver Utilities C source code (uvcopy,uvsort,etc)
 :-----tf                  - test files for various demos (similar to dat1/...)
 :-----tmp                 - tmp subdir (test/demo outputs)
 :-----tmp1                - misc, outdir for some utility scripts (like listall1)
 :-----tmp2

Please copy the demo/... files to your homedir to avoid conflict with other users of Vancouver Utilities & to avoid losing your files when new versions of Vancouver Utilities are installed.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4B3. Vancouver Utility Programming Aids - TableJobs

copy $UV/demo to your $HOME/demo/...


 #1. Login userxx --> /home/userxx

 #2. mkdir demo   <-- make directory for copy of demo files in your homedir

 #3. cp -r /home/uvadm/demo/* demo/   <-- copy demo files to your homedir
     ==============================

 #4. cd demo    <-- change into demo/ directory
     =======

TableJobs demo files copied to your homedir

Here is an expanded view of the most relevant subdirs/files required for the TableJob demos (omitting subdirs/files used for other parts of UVdemos.doc).

 /home/userxx/demo/              - copy to your homedir, cd demo/ & run jobs there
 :-----dat1                    <-- testdata files for demos
 :     :-----CanadaMPs.csv       - CAnadian Members of Parliament (csv file)
 :     :-----CanadaMPs           - desired fields extracted to fixed positions
 :     :-----UScongress.csv    <-- US Congress Representatives & Senators (csv file)
 :     :-----UScongress          - desired fields extracted to fixed positions
 :     :-----UScities.tab      <-- US top 300 cities & populations (tab separated)
 :     :-----UScitiesb           - desired fields extracted to fixed positions
 :     :-----USstates.csv      <-- US top 300 cities & populations (csv file)
 :     :-----UScitiesb           - desired fields extracted to fixed positions
 :     :
 :-----ftps                    <-- FTP scripts
 :     :ftpdemo1a
 :     :ftpdemo1b
 :     :ftpget1
 :     :ftpput1
 :-----jcl2
 :     :-----jar200.jcl
 :     :-----...etc...
 :-----pf                      <-- uvcopy job Parameter Files, see note below
 :     :-----                    - you could create your own uvcopy jobs here
 :     :
 :-----stats                   <-- output directory for TableJob Reports
 :     :-----ftps_open          - table summary of IP#s in FTP scripts
 :     :-----ftps_user          - userids & passswords in FTP scripts
 :     :-----sqls_FROM           - Table-Names in SQL scripts
 :     :-----CanadaMPs_Province  - Canadian MPs tabled by Province
 :     :-----...                 - Report-Names created from Directory + Keyword
 :     :-----...
 :-----sqls                    <-- SQL scripts for TableJob demos
 :     :-----customers_all
 :     :-----customers_basic
 :     :-----...etc...
 :-----tmp
 :-----tmp1                    <-- tmp dirs for temp files, sorts, etc
 :-----tmp2

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4C1. TableJobs - Table Summary Counts from Keywords/Targetwords

table3d options

The 1st example on page '4A1' did not show the options screen displayed before table3d is executed & the report written to the stats/ directory. Here are the options which we will omit from following examples.


 uvcopy table3d,fild1=dir,arg1=keyword,arg2=qual1,arg3=qual2,arg4=chars2blank,uop=...
 ====================================================================================
 uop=a0b3c0d0f0j0l0m0p1s0t0x0w1 - option defaults
     a1                     - "&" variable end "#", insert space after "#" force endword
     a2                     - discard variables not ending with "#"
       b3                   - assume 1 blank before & after keyword (default)
       b1                   - assume 1 blank before & no blank after
       b2                   - assume no blank before & 1 blank after
       b0                   - assume no blank before or after
         c1                 - bypass comments in COBOL programs (* column 7)
         c2                 - bypass comments in shell scripts (# column 1)
         c4                 - bypass comments in JCL //* cols 1-3
           d1               - insert blank before "$", allow arg1=$
             f0             - do not prepend/append table entry with filename
             f1             - prepend table argument with filename
             f2             - append table argument with filename
               j1           - insert blank before search word in data
               j2           - insert blank after search word in data
               j3           - insert blank & after search word in data
                 l0         - translate lower case before matching patterns
                   m1       - look for multiple keywords on 1 line
                   m2       - look for multiple keywords & prepend targetword
                   m4       - look for multiple keywords & append targetword
                     p1     - qualifier#1 present & qualifier#2 absent
                     p2     - qualifier#1 present & qualifier#2 present
                     p4     - qualifier#1 absent  & qualifier#2 absent
                       s1   - case insensitive translate patterns to lowercase
                         t1 - translate data to lower-case (to match patterns)
                         t2 - translate data to UPPER-case
                     w1     - target word is 1st word following keyword
                     w2     - target word is 2nd word following keyword
                     w0     - target word is same as keyword (partial def)
                   x1       - table w# word (search will ignore keyword)
                            - specified keyword will be inserted in outfilename
                 y#         - table multiple target words (4 max)
                 y3         - would table target word + following 2 words
               z1           - convert x"A3" to "@" in data for keyword "@"
 arg4 special chars to separate words, use with option w# offset from search word
 ------> &\,$=/>-<:(.`);_*[|]" <--- arg4 punctuation chars to blank
 ------> abcdefghjklpqrsuALPRQ <--- OR enter equivalents OR enter "all"
 User OPtion defaults=q1a0b3c0d0f0j0l0m0p1s0t0x0w1 (null accept or enter overrides)
 enter arg1 search keyword ------------>  <-- prompt inhibited if arg1 on cmdline
 enter arg2 qualifier#1 (or null) ----->
 enter arg3 qualifier#2 (or null) ----->
 enter arg4 chars to blank before word sep (comma,equal,etc) -->
 EOJ, Output File written to: stats/ftps_open
 default command = null, OR enter: vi,cat,more,lp,uvlp12,etc -->

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4D1. TableJobs - Table Summary Counts from Keywords/Targetwords

most useful options - option 'w#'

Option 'w' is probably the most useful options you are likely to need. Option w specifies the word# offset of the TargetWord from the KeyWord. It defaults to 'w1' meaning the TargetWord to be tabled & counted is the 1st word following the search KeyWord specified by arg1=...

Here is an example using option w2 (2nd word following search KeyWord) to table summarize the output filenames on FTP 'put' commands. First we will show the relevant FTP script (parm file)

# ftps/ftpdemo1b open 192.168.0.5 user user02 user02pw lcd data1 put ar.sales.items C:\AR\SALES.ITEMS put gl.account.tran1 D:\GL\ACCOUNT.TRAN1

Example#2 - option 'w#' TargetWord offset from KeyWord


 #2. uvcopy table3d,fild1=ftps,arg1=put,uop=w2
     ==========================================
      - search ftps/* for keyword 'put' & table 2nd word following
Note
  • you will get prompts for unused arguments, enter null to continue
  • but I suggest reply 'cat' for the last prompt (to display output report)
      ---> cat <-- reply 'cat' to display report

 #2a. vi stats/ftps_put_w2  <-- OR, enter separate command if job already ended
      =====================
 # /home/uvadm/demo/stats/ftps_put_w2  <-- This report created by uvcopy:
 # uvcopy table3d,fild1=ftps,arg1=put,uop=w2
 # =====================================================================
 # - scan all files in directory for a keyword & table counts of following word
 # InDir=ftps  Keyword=put  Qual1=   Qual2=
 # WordSepsBlank=  Options=q1a0b3c0d0f0j0l0m0p1s0t0x0w1w2
 # - default selects following word, use option w2 to table 2nd word, etc
 # - specify arg1=Keyword,arg2=Qualifier-present,arg2=Qualifier-absent
 # &\,$=/>-<:(.');_*[|]"<--- arg4=punctuation to blank for word separation
 # abcdefghjklpqrsuALPRQ<--- equivalents, easier to enter, may enter "all"
 # Date=2019/06/17_15:11:03, Site=UV_Software, Host=uvsoft5, User=uvadm
 #===============================================================================
 #---> uvcopy table3d,fild1=ftps,arg1=put,uop=w2
 table3d  2019/06/17_15:11:05  Counts by Targetword following specified Keyword
 tbl#0001  tblt1f7 e0(48)         argument
 line#   1strec#  %      count  target-word
     1         6  50         1   C:\AR\SALES.ITEMS
     2         7  50         1   D:\GL\ACCOUNT.TRAN1
                 100         2*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4D2. TableJobs - Table Summary Counts from Keywords/Targetwords

most useful options - option 'y#'

Option 'y#' specifies the number of keywords to be tabled (default 1). For example, we can table summarize both userid & password following keyword 'user'. Here is a sample using the FTP ftps/* but showing only 1st file:

# ftps/ftpdemo1a open 192.168.0.4 user user01 user01pw lcd data1 get /home/userxx/ar/sales.items get /home/userxx/gl/account.tran1

Example#3 - option 'y' no of words to table


 #3. uvcopy table3d,fild1=ftps,arg1=user,uop=y2
     ===========================================
     - search ftps/* for keyword 'user' & table following 2 words
     --> cat <-- enter at last prompt to display report
 # /home/uvadm/demo/stats/ftps_user_y2  <-- This report created by uvcopy:
 # uvcopy table3d,fild1=ftps,arg1=user,uop=y2
 # =====================================================================
 # - scan all files in directory for a keyword & table counts of following word
 # InDir=ftps  Keyword=user  Qual1=   Qual2=
 # WordSepsBlank=  Options=q1a0b3c0d0f0j0l0m0p1s0t0x0w1y2
 # - default selects following word, use option w2 to table 2nd word, etc
 # - specify arg1=Keyword,arg2=Qualifier-present,arg2=Qualifier-absent
 # &\,$=/>-<:(.');_*[|]"<--- arg4=punctuation to blank for word separation
 # abcdefghjklpqrsuALPRQ<--- equivalents, easier to enter, may enter "all"
 # Date=2019/06/17_15:31:41, Site=UV_Software, Host=uvsoft5, User=uvadm
 #===============================================================================
 #---> uvcopy table3d,fild1=ftps,arg1=user,uop=y2
 table3d  2019/06/17_15:31:44  Counts by Targetword following specified Keyword
 tbl#0001  tblt1f7 e0(48)         argument
 line#   1strec#  %      count  target-word
     1         3  25         1  user01 user01pw
     2         3  25         1  user02 user02pw
     3         3  50         2  user03 user03pw
                 100         4*  *TOTAL*

Notes

  1. Note that any options entered are concatenated onto the output filename (ftps_user_y2 for the example above). The 1st line of the report file shows you the full path name of the report file (see above).

  2. For these demos, we are coding all arguments & options on the command-line, but you could enter only 'uvcopy table3d' & enter relevant info at the prompts. When we enter all relevant info on the cmdline, we can just make null replies to all following prompts.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4E1. Vancouver Utility Programming Aids - TableJobs

table3d - create summary table of programs in JCL

This the 4th demo of table3d, now using JCL as input


 #40a. login userxx --> /home/userxx
 #40b. cd demo      --> /home/usrxx/demo

 #1. l    <-- list files in current directory /home/userxx/demo
     ===
      drwxrwxr-x 2 userxx apps 4096 Feb 13 15:01 jcl2
      drwxrwxr-x 2 userxx apps 4096 Feb 13 15:01 jcl3
      drwxrwxr-x 2 userxx apps 4096 Feb 13 06:47 ftps
      drwxrwxr-x 2 userxx apps 4096 Feb 13 06:47 rpts
      drwxrwxr-x 2 userxx apps 4096 Feb 13 06:47 sqls
      drwxrwxr-x 2 userxx apps 4096 Feb 13 15:04 stats

 #2. l jcl2   <-- list jcl2/* input files for 3rd demo of table3d
     ======
      -rw-rw-r-- 1 userxx apps  480 Feb 13 15:01 jar100.jcl
      -rw-rw-r-- 1 userxx apps  613 Feb 13 15:01 jar120.jcl
      -rw-rw-r-- 1 userxx apps  984 Feb 13 15:01 jar200.jcl
      -rw-rw-r-- 1 userxx apps  777 Feb 13 15:01 jgl100.jcl
      -rw-rw-r-- 1 userxx apps 1238 Feb 13 15:01 jgl200.jcl  <-- listed below
      -rw-rw-r-- 1 userxx apps  617 Feb 13 15:01 jgl220.jcl
      -rw-rw-r-- 1 userxx apps 1477 Feb 13 15:01 jgl230.jcl
      -rw-rw-r-- 1 userxx apps  932 Feb 13 15:01 jgl320.jcl

 #3. cat jcl2/jar200.jcl
     ===================
      //JAR200   JOB  (1234),'TEST/DEMO MVS JCL CONVERT'
      //* SORT AR.SALES.ITEMS BY PRODUCT CODE FOR LISTING
      //STEP010  EXEC PGM=SORT,REGION=2048K                      <--Note PGM=SORT
      //SORTIN   DD DSN=AR.SALES.ITEMS,DISP=SHR
      //SORTOUT  DD DSN=&&jar200_TEMPSLS,DISP=(NEW,PASS),...
      //SYSIN    DD *
      SORT FIELDS=(31,6,CH,A,1,6,CH,A)
      OMIT COND=((11,2,CH,EQ,C'65'),OR,(COND=(11,2,CH,EQ,C'85'))
      /*
      //*=================== step# 0020 ========================
      //* LIST AR.SALES.ITEMS IN PRODUCT CODE SEQUENCE
      //STEP020  EXEC PGM=CAR200,COND=(4,LT,STEP010),PARM=2006   <--Note PGM=CAR200
      //SALEDTL  DD DSN=&&jar200_TEMPSLS,DISP=(OLD,PASS)
      //CUSTMAS  DD DSN=AR.CUSTOMER.MASTER.INDEXED,DISP=SHR
      //SALELST  DD DSN=AR.SALES.LIST,DISP=(,CATLG,DELETE),...

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4E2. Vancouver Utility Programming Aids - TableJobs

table summarize PGM names in JCL


 #4. uvcopy table3d,fild1=jcl2,arg1=PGM,arg4=ec,uop=w1q0i7
     =====================================================
     - read following notes before executing above command line

Notes re arguments & options

We will use 'PGM' as the keyword to table summarize the following word (program-name).

Re: 'arg4=ec', arg4 specifies characters to blank before word separation. Some special characters are a problem when entered using arg4=... on the command line so we allow you to use an alpha character to represent & show the equivalents as follows:

&\,$=/-:(.`);_*|" <--- arg4=special chars to blank abcdefhklpqrsuAPQ <--- equivalents, easier to enter

 //STEP010  EXEC PGM=CAR100,PARM=2008

For the above example, we need to blank the '=' & the ',' to separate the words. We used 'arg4=ec' vs 'arg4==,', because the ',' would confuse command parsing.

You could omit arg4=... from command line & enter actual characters at the prompt, but we prefer entering on the command line for this documentation.

Re: options 'uop=w1q0i7'. 'w1' means the target-word is 1st word following the keyword & we did not need to specify since it is the default.

Option 'q0' inhibits prompting for options (not needed if on the command line).

Option 'i7' inhibits displaying the 'opr' commands describing options, etc.

now execute the table3d command


 #5. uvcopy table3d,fild1=jcl2,arg1=PGM,arg4=ec,uop=q0i7
     ===================================================
 enter arg2 qualifier#1 (null=disable) ----->
 enter arg3 qualifier#2 (null=disable) ----->
 enter strings to be removed (use ":" sep if multiple) --->
 190216:160322:table3d: EOF fili01 rds=34 size=1477: demo/jcl2/jgl230.jcl hits=3
 190216:160322:table3d: EOF fili01 rds=23 size=984: demo/jcl2/jar200.jcl hits=2
 190216:160322:table3d: EOF fili01 rds=11 size=480: demo/jcl2/jar100.jcl hits=1
 190216:160322:table3d: EOF fili01 rds=17 size=777: demo/jcl2/jgl100.jcl hits=1
 190216:160322:table3d: EOF fili01 rds=26 size=1238: demo/jcl2/jgl200.jcl hits=2
 190216:160322:table3d: EOF fili01 rds=22 size=932: demo/jcl2/jgl320.jcl hits=2
 190216:160322:table3d: EOF fild01 rds=8 size=4096: demo/jcl2 hits=11
 190216:160322:table3d: EOF filo01 wrts=20 size=1322: stats/jcl2_PGM_ec_w1q0i7
 EOJ, Output File written to: stats/jcl2_PGM_ec_w1q0i7
 default command = null, OR enter: more,print,uvlpr12,edit,null -->

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

table3d output report for 3rd demo JCL programs


 #5a. --> cat <-- suggest 'cat' to view outputs (when known to be small)
          ===   - alternative, null reply & use #35. below (vi stats/ftps_open)
 # /home/uvadm/demo/stats/jcl2_PGM_ec_w1q0i7  <-- This report created by uvcopy:
 # uvcopy table3d,fild1=jcl2,arg1=PGM,arg4=ec,uop=w1q0i7
 # =====================================================================
 # - scan all files in directory for a keyword & table counts of following word
 # InDir=jcl2  Keyword=PGM  Qual1=   Qual2=
 # WordSepsBlank==,  Options=q1a0b3c0d0f0j0l0m0p1s0t0x0w1w1q0i7
 # - default selects following word, use option w2 to table 2nd word, etc
 # - specify arg1=Keyword,arg2=Qualifier-present,arg2=Qualifier-absent
 # &\,$=/>-<:(.');_*[|]"<--- arg4=punctuation to blank for word separation
 # abcdefghjklpqrsuALPRQ<--- equivalents, easier to enter, may enter "all"
 # Date=2019/06/17_16:06:56, Site=UV_Software, Host=uvsoft5, User=uvadm
 #===============================================================================
 #---> uvcopy table3d,fild1=jcl2,arg1=PGM,arg4=ec,uop=w1q0i7
 table3d  2019/06/17_16:06:59  Counts by Targetword following specified Keyword
 tbl#0001  tblt1f7 e0(48)         argument
 line#   1strec#  %      count  target-word
     1         3   7         1   CAR100
     2        15   7         1   CAR200
     3        26  21         3   CGL100
     4        17  21         3   CGL200
     5        12   7         1   IDCAMS
     6         6   7         1   IEBGENER
     7         5  28         4   SORT
                 100        14*  *TOTAL*

 #6. vi stats/jcl2_PGM_ec_q0i7  <-- view report separately after running table3d
     =========================
Note
  • the report is named for the directory + keyword + separators + options
  • so you do not have to make up a unique name to avoid overwriting prior report

General Purpose Utilities

The example above used JCL to test/demo table3d. You may not have JCL, but we hope you can see how table3d might be used with whatever files, scripts,& programming languages you have.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4F1. Vancouver Utility Programming Aids - TableJobs

Writing specific uvcopy jobs vs Pre-Programmed TableJobs

Pre-programmed jobs make it easy to apply the power of uvcopy to your data files - analyzing, selecting, searching/replacing, etc with more options & qualifications than are provided by the standard unix/linux system utilities.

You do not need to know the uvcopy instruction set to use general purpose pre-programmed jobs such as 'table3d', but we hope you will eventually learn the uvcopy instruction set, in case you find situations where you need more specific custom processing.

You can see the instruction file for 'table3d' at /home/uvadm/pf/util/table3d. It has over 250 instructions because it processes all files in a directory & provides many options to search for & qualify keywords & build summary tables of following targetwords.

We will show you here a much simpler table summary job (tableIP) with only 14 essential instructions that creates the same table summary as did the previous 'table3d', but it is written specifically to table summarize IP#s following the word 'open' (vs the general purpose pre-programmed table3d which accepts arguments to summarize any desired item following any desired keyword for all files in directory).

'tableIP' is stored at /home/uvadm/pf/demo/tableIP & you can run as follows:


 #0a. login userxx --> /home/userxx
 #0b. cd demo      --> /home/usrxx/demo

 #1. cat ftps/* >tmp1/ftps_all   <-- concat all ftps/* into 1 file for tableIP
     ===========================

 #2. uvcopy tableIP,fili1=tmp1/ftps_all,filo1=stats/ftps_ftp_tableIP
     =================================================================
     - run demo job to summarize IP#s in 1 file & create table same as previous table3d
 tableIP  2019/06/17_16:22:09  table summarize TCP/IP#s in FTP scripts
 tbl#0001  tblt1   ax6(16)        argument
 line#   1strec#  %      count  FTP IP#
     1         2  25         1  192.168.0.4
     2         9  25         1  192.168.0.5
     3        17  50         2  192.168.0.6
                 100         4*  *TOTAL*

Since this reads only 1 file (vs all files in directory), we concatenate the ftps/* files used for the previous table3d demo into 1 file stored in tmp1/ftps_all for input to uvcopy tableIP.

See the 'tableIP' demo job listed on the next page:

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4F2. Writing customized uvcopy job vs Pre-Programmed TableJobs

tableIP - simplified table summary demo

 # tableIP - table summarize counts of unique TCP/IP#s in FTP scripts
 #         - uvcopy demo job, by Owen Townsend, UV Software, June 2019
 # - demo job used in documentation www.uvsoftware.ca/uvdemos.htm#2C1
 # - Normally use pre-programmed job 'table3d' to crate summary table counts
 # - This illustrates writing the uvcopy code for a custom table summary
 # - to keep it simple we will 1st concat all parm files into 1 tmp file
 #
 # cat ftps/* >tmp1/ftps_all
 #
 # uvcopy tableIP,fili1=tmp1/ftps_all,filo1=stats/ftps_ftp_tableIP
 # =================================================================
 # - read input fili1=..., build table of IP#s, write to output filo1=... at EOF
 #
 # uvcopy tableIP    <-- same as above, I/O files default as above
 # ==============
 #
 # 'tableIP' is a simplified version of 'table3d' general purpose pre-programmed job
 #   - to create table summaries of ANY item from ALL files in a directory
 #   - without needing to write any uvcopy instructions
 # 'tableIP' builds a table of 1 specific item from only 1 file
 #   - intended to demo the power of uvcopy & encourage you to learn uvcopy
 #   - will scan for lines with word 'open' known to precede the IP#
 #     open 192.168.0.4   <-- scan for lines with 'open' known to precede the IP#
 #
 opr='$jobname - table summarize TCP/IP#s in FTP scripts'
 rop=r1  # option for EOF prompt to view output file
 fili1=?tmp1/ftps_all,rcs=256,typ=LST
 filo1=?stats/ftps_tableIP,rcs=256,typ=LSTt
 @run
         opn     all
 # begin loop to get records, build table until EOF
 man20   get     fili1,a0               get next record into area 'a'
         skp>    man90                  (cc set > at EOF)
         sqzl1c1 a0(80),' '             ensure column 1 blank & 1 blank between words
         scn     a0(80),' open '        scan for ' open ' preceding IP#
         skp!    man20                  if not found - return to get next record
         tblt1   ax6(16),'FTP IP#'      build table counts in memory
         skp     man20                  return to get next record
 # EOF - dump table to output file, close files, end job
 man90   tbpt1  filo1,'table summarize TCP/IP#s in FTP scripts'
         cls    all
         eoj
 #            ** output - stats/ftps_tableIP **
 # tableIP  2019/06/17_16:22:09  table summarize TCP/IP#s in FTP scripts
 # tbl#0001  tblt1   ax6(16)        argument
 # line#   1strec#  %      count  FTP IP#
 #     1         2  25         1  192.168.0.4
 #     2         9  25         1  192.168.0.5
 #     3        17  50         2  192.168.0.6
 #                 100         4*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4F3. Writing customized uvcopy job vs Pre-Programmed TableJobs

Notes re uvcopy demo job 'tableIP'

  1. You could use this demo job to learn a lot about the uvcopy instruction set.

  2. Some uvcopy instructions are similar to the IBM 360 Z/OS instruction set, but uvcopy instructions are interpretive, no compile required, just edit & run.

  3. uvcopy gives you the power of assembler in an easy to use framework. In particular, this example will illustrate the power of index registers. uvcopy operands consist of an area (a-z), optionally an index register (a-z), and a displacement. For example op1 of 'tbl' is 'ax6' which is area 'a' + the displacement in register 'x' + the displacement coded ('6') in this example.

  4. 'scn' finds the word ' open ' & stores its displacement in index register 'x', which is then used as by 'tbl' to address the following word for tabling.

  5. All instructions are documented at https://uvsoftware.ca#uvcopy3.htm. The 1st page is an index with links to the various instructions. Here are direct links to some of the more interesting instructions in tableIP.
      https://uvsoftware.ca#uvcopy3.htm#sqz
      https://uvsoftware.ca#uvcopy3.htm#scn
      https://uvsoftware.ca#uvcopy3.htm#tbl
      https://uvsoftware.ca#uvcopy3.htm#tbp
  1. Here are a few notes about these 4 instructions use in tableIP & you can click on the links to see the complete doc on any of these.

 #7a. sqzl1c1 a0(80),' '             ensure column 1 blank & 1 blank between words
      =================
      - option 'l1' of sqzl1c1 ensures 1 blank in column 1
      - option 'c1' of sqzl1c1 ensures 1 blank between words
        so we can know that the word following ' open ' is exactly 6 bytes higher

 #7b. scn     a0(80),' open '        scan for ' open ' preceding IP#
      =======================
      - scn stores the zero relative displacement of ' open ' in register x.

 #7c. tblt1   ax6(16),'FTP IP#'      build table counts in memory
      ========================
      - tblt1 builds table#1 based on the 16 bytes located in area 'a'
        + displacement in register 'x' + 6 = area 'a' + 0 + 6
      - in this example, register 'x' will be 0 for ' open 192.168.0.4 ' since 'open'
        was 1st word on the line & sqzl1c1 has ensured the leading ' ' matching ' open '

 #7d. tbpt1  filo1,'table summarize TCP/IP#s in FTP scripts'
      ======================================================
      - tbpt1 (at EOF) dumps (Prints) the table to the output file (filo1=... above)
      - operand 2 supplies an extra heading for the table.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4G1. Vancouver Utility Programming Aids - TableJobs

table3d - create summaries of TABLEs in SQL scripts


 #0a. login userxx --> /home/userxx
 #0b. cd demo      --> /home/usrxx/demo

 #1a. l        <-- list subdirs in /home/userxx/demo
      ===
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 ctl
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 jcl2
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 jcl3
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 ftps
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 rpts
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 sqls     <-- SQL scripts
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 stats
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 tmp
      drwxrwxr-x 2 userxx apps 4096 Feb 16 17:16 tmp1

 #1b. l sqls     <-- list files in sqls/...
 ===========
      -rw-rw-r-- 1 userxx apps  56 Feb 16 17:16 select_customers_all    <-- simple example
      -rw-rw-r-- 1 userxx apps  97 Feb 16 17:16 select_customers_basic
      -rw-rw-r-- 1 userxx apps 152 Feb 16 17:16 select_customers_WA     <-- PROBLEM
      -rw-rw-r-- 1 userxx apps 136 Feb 16 17:16 select_employee_dept123
      -rw-rw-r-- 1 userxx apps 115 Feb 16 17:16 select_employee_seattle
      -rw-rw-r-- 1 userxx apps  79 Feb 16 17:16 select_orders_big
      -rw-rw-r-- 1 userxx apps 306 Feb 16 17:16 select_patients_old
      -rw-rw-r-- 1 userxx apps 129 Feb 16 17:16 select_students_failing

We can use the known keyword 'FROM' to summarize the following SQL TABLE-name. For example here is the SELECT stmnt from the 1st of the 8 listed on the enxt page.

      SELECT * FROM Customers;

PROBLEM in SQL scripts for table3d

table3d expects the target-word (to be summarized) to follow the known key-word on the same line, as above, but consider the SELECT in the 3rd SQL script:

       SELECT custno, Name, Birthday, Phone,
       Address, City, Zip, LYsales,Discount, FROM   <-- PROBLEM
       Customers WHERE LYsales >5000;

Our solution is to precede 'table3d' with 'combine1d' which will combine the SQL SELECT statements onto 1 line using the ';' semi-colons that end SQL statements.

Note that combine1d is a general purpose utility & could be used for other file types that you may have. 'arg1=...' defines optional statement begin patterns (up to 8 words) and 'arg4=..." defines the END patterns (up to 8 words, not necessarily 1 character).

'arg2=...' defines up to 8 words that must be present for the stmnt to be selected and 'arg3=...' defines up to 8 words that must be absent for the stmnt to be selected.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4G2. Creating table summaries of SQL TABLEs in SQL scripts

SQL scripts listed - test files for table3d


 #1c. cat sqls/*  <-- display contents of files in sqls/...
      ==========
      /* select_customers_all */
         SELECT * FROM Customers;
      /* select_customers_basic */
         SELECT Name, Phone, Address, City, Zip
                FROM Customers;
      /* select_customers_sales10000 */
         SELECT custno, Name, Phone, Address, City, Zip,
         TYsales, LYsales, FROM
         Customers WHERE LYsales >10000;
      /* select_customers_sales20000 */
         SELECT custno, Name, Phone, Address, City, Zip,
         TYsales, LYsales, FROM
         Customers
         WHERE LYsales >20000;
      /* select_employee_dept123 */
         SELECT EMP_ID, NAME, ADDRESS, CITY, ZIP, DEPT FROM
         EMPLOYEE WHERE DEPT = '123' AND STATUS = 'FULL';
      /* select_employee_seattle */
         SELECT EMP_ID, LAST_NAME FROM EMPLOYEE
         WHERE CITY = 'Seattle' ORDER BY EMP_ID;
      /* select_orders_big */
         SELECT item FROM Orders
         WHERE quantity > 500;
      /* select_patients_over70 */
         SELECT Name, Age FROM Patients WHERE Age > 70
         GROUP BY Age ORDER BY Name;
      /* select_patients_over80 */
         SELECT Name, Age FROM Patients WHERE Age > 80
         GROUP BY Age ORDER BY Name;
      /* select_patients_over90 */
         SELECT Name, Age FROM Patients
         WHERE Age > 90 GROUP BY Age ORDER BY Name;
      /* select_students_failing */
         CREATE VIEW Failing_Students AS
         SELECT S_NAME, Student_ID FROM
         STUDENT WHERE GPA < 30;

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4G3. Creating table summaries of SQL TABLEs in SQL scripts

table3d summarize SQL TABLEs - 1st attempt


 #2. uvcopy table3d,fild1=sqls,arg1=FROM,uop=q0i7
     ============================================
      enter arg2 qualifier#1 (null=disable) ----->
      enter arg3 qualifier#2 (null=disable) ----->
      enter arg4 chars to blank before word sep (comma,equal,etc) -->
      190617:173959:table3d: EOF fili01 rds=3 size=77: sqls/select_orders_big hits=1
      190617:173959:table3d: EOF fili01 rds=3 size=109: sqls/select_patients_over80 hits=1
      190617:173959:table3d: EOF fili01 rds=2 size=55: sqls/select_customers_all hits=1
      190617:173959:table3d: EOF fili01 rds=3 size=97: sqls/select_customers_basic hits=1
      190617:173959:table3d: EOF fili01 rds=3 size=115: sqls/select_employee_seattle hits=1
      190617:173959:table3d: EOF fili01 rds=3 size=110: sqls/select_patients_over70 hits=1
      190617:173959:table3d: EOF fili01 rds=5 size=152: sqls/select_customers_sales20000
      190617:173959:table3d: EOF fili01 rds=3 size=110: sqls/select_patients_over90 hits=1
      190617:173959:table3d: EOF fili01 rds=4 size=147: sqls/select_customers_sales10000
      190617:173959:table3d: EOF fili01 rds=4 size=126: sqls/select_students_failing
      190617:173959:table3d: EOF fili01 rds=3 size=136: sqls/select_employee_dept123
      190617:173959:table3d: EOF fild01 rds=13 size=4096: sqls hits=7
      190617:173959:table3d: EOF filo01 wrts=17 size=1240: stats/sqls_FROM_q0i7
      EOJ, Output File written to: stats/sqls_FROM_q0i7
      default command = null, OR enter: more,print,uvlpr12,edit,null --> cat
      # /home/uvadm/demo/stats/sqls_FROM  <-- This report created by uvcopy:
      # uvcopy table3d,fild1=sqls,arg1=FROM
      # =====================================================================
      # - scan all files in directory for a keyword & table counts of following word
      # InDir=sqls  Keyword=FROM  Qual1=   Qual2=
      # WordSepsBlank=  Options=q1a0b3c0d0f0j0l0m0p1s0t0x0w1
      # - default selects following word, use option w2 to table 2nd word, etc
      # - specify arg1=Keyword,arg2=Qualifier-present,arg2=Qualifier-absent
      # &\,$=/>-<:(.');_*[|]"<--- arg4=punctuation to blank for word separation
      # abcdefghjklpqrsuALPRQ<--- equivalents, easier to enter, may enter "all"
      # Date=2019/06/17_11:45:06, Site=UV_Software, Host=uvsoft5, User=uvadm
      #===============================================================================
      #---> uvcopy table3d,fild1=sqls,arg1=FROM
      table3d  2019/06/17_11:45:09  Counts by Targetword following specified Keyword
      tbl#0001  tblt1f7 e0(48)         argument
      line#   1strec#  %      count  target-word
          1         2  28         2   Customers;
          2         2  14         1   EMPLOYEE
          3         2  14         1   Orders
          4         2  42         3   Patients
                      100         7*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4G4. Creating table summaries of SQL TABLEs in SQL scripts

PROBLEM - some TABLE names missing ?

If you compare our 1st attempt above to the 8 sql files listed on page '4G2', you will see that there are some missing. This 1st attempt found only 4 TABLE-names, & 6 occurrences, but we know there are 5 TABLE-names & 8 occurrences.

'table3d' expects the target-word to be summarized (SQL TABLEname in this case) to directly follow the keyword ('FROM' in this case), BUT in 4 of the 8 sql files, the keyword 'FROM' is on the line preceding the line with the target-word (TABLEname).

Solution - combine SQL statements onto 1 line

We will use the uvcopy 'combine1d' utility to combine SQL statements onto 1 line. 'arg1=SELECT' will start combining lines until the 'arg4=;' end-of-stmnt marker found and 'arg2=FROM' must occur somewhere within the combined statement.

We will use 'combine1d' to combine the SQL stmnts from all files in sqls/... and write the output combined stmnts into the tmp1/... directory as follows:


 #2. uvcopy "combine1d,fild1=sqls,fild2=tmp1,arg1=SELECT,arg2=FROM,arg4=;,uop=d1r1"
     ==============================================================================
      - copy all files in sqls/* to tmp1/... combining multi-line SQL stmnts onto 1 line
      - we use option 'd1' to drop /*...comments...*/ & option 'r1' to remove the ';' marker
      uop=d1r0t0 - option defaults
          d1     - drop /* ...comments... */
            r1   - remove stmt end pattern
              t0 - no case translation (t1 lower, t2 UPPER)
 190617:172639:combine1d: EOF filo02 wrts=1 size=24: tmp1/select_customers_all
 190617:172639:combine1d: EOF filo02 wrts=1 size=54: tmp1/select_customers_basic
 190617:172639:combine1d: EOF filo02 wrts=1 size=102: tmp1/select_customers_sales10000
 190617:172639:combine1d: EOF filo02 wrts=1 size=102: tmp1/select_customers_sales20000
 190617:172639:combine1d: EOF filo02 wrts=1 size=99: tmp1/select_employee_dept123
 190617:172639:combine1d: EOF filo02 wrts=1 size=78: tmp1/select_employee_seattle
 190617:172639:combine1d: EOF filo02 wrts=1 size=45: tmp1/select_orders_big
 190617:172639:combine1d: EOF filo02 wrts=1 size=73: tmp1/select_patients_over70
 190617:172639:combine1d: EOF filo02 wrts=1 size=73: tmp1/select_patients_over80
 190617:172639:combine1d: EOF filo02 wrts=1 size=73: tmp1/select_patients_over90
 190617:172639:combine1d: EOF filo02 wrts=1 size=54: tmp1/select_students_failing
 190617:172639:combine1d: EOF fild01 size=4096: sqls
 190617:172639:combine1d: EOF fild02 size=4096: tmp1

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4G5. Creating table summaries of SQL TABLEs in SQL scripts

Problem Solution - Combine SELECT stmnts onto 1 line


 #2a. cat tmp1/*    <-- list contents of all output files from combine1d
      ==========
      SELECT * FROM Customers
      SELECT Name, Phone, Address, City, Zip FROM Customers
      SELECT custno, Name, Phone, Address, City, Zip, TYsales, LYsales, FROM Customers WHERE LYsales >10000
      SELECT custno, Name, Phone, Address, City, Zip, TYsales, LYsales, FROM Customers WHERE LYsales >20000
      SELECT EMP_ID, NAME, ADDRESS, CITY, ZIP, DEPT FROM EMPLOYEE WHERE DEPT = '123' AND STATUS = 'FULL'
      SELECT EMP_ID, LAST_NAME FROM EMPLOYEE WHERE CITY = 'Seattle' ORDER BY EMP_ID
      SELECT item FROM Orders WHERE quantity > 500
      SELECT Name, Age FROM Patients WHERE Age > 70 GROUP BY Age ORDER BY Name
      SELECT Name, Age FROM Patients WHERE Age > 80 GROUP BY Age ORDER BY Name
      SELECT Name, Age FROM Patients WHERE Age > 90 GROUP BY Age ORDER BY Name
      SELECT S_NAME, Student_ID FROM STUDENT WHERE GPA < 30
Note
  • options default to d1 (drop /*...comments...*/) & r1 (remove end stmnt ';')
  • you might rerun using option d0 (to NOT drop /*...cmts...*/)
  • which would report SELECT stmnt TABLEs found in /*...cmts...*/

rerun table3d inputting SELECT stmnts combined onto 1 line

We will specify the input directory as tmp1/... (output from combine1d above), and the report output of table3d defaults to stats/... Note that table3d names the output report by concatenating the input directory (now tmp1) + the keyword, so in this case the output report will be 'stats/tmp1_FROM'. You could name the output file by adding 'filo1=sqls_FROM' to the command below:

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4G6. Creating table summaries of SQL TABLEs in SQL scripts

rerun table3d to create correct summary of SQL TABLEs


 #3. uvcopy table3d,fild1=tmp1,arg1=FROM,uop=q0i7
     ============================================
      enter arg2 qualifier#1 (null=disable) ----->
      enter arg3 qualifier#2 (null=disable) ----->
      enter arg4 chars to blank before word sep (comma,equal,etc) -->
      190617:173015:table3d: EOF fili01 rds=1 size=45: tmp1/select_orders_big hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=73: tmp1/select_patients_over80 hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=24: tmp1/select_customers_all hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=54: tmp1/select_customers_basic hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=78: tmp1/select_employee_seattle hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=73: tmp1/select_patients_over70 hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=102: tmp1/select_customers_sales20000 hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=73: tmp1/select_patients_over90 hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=102: tmp1/select_customers_sales10000 hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=54: tmp1/select_students_failing hits=1
      190617:173015:table3d: EOF fili01 rds=1 size=99: tmp1/select_employee_dept123 hits=1
      190617:173015:table3d: EOF fild01 rds=13 size=4096: tmp1 hits=11
      190617:173015:table3d: EOF filo01 wrts=18 size=1279: stats/tmp1_FROM_q0i7
      EOJ, Output File written to: stats/tmp1_FROM_q0i7
      default command = null, OR enter: vi,cat,more,lp,uvlp12,etc -->
      # /home/uvadm/demo/stats/tmp1_FROM  <-- This report created by uvcopy:
      # uvcopy table3d,fild1=tmp1,arg1=FROM
      # =====================================================================
      # - scan all files in directory for a keyword & table counts of following word
      # InDir=tmp1  Keyword=FROM  Qual1=   Qual2=
      # WordSepsBlank=  Options=q1a0b3c0d0f0j0l0m0p1s0t0x0w1
      # - default selects following word, use option w2 to table 2nd word, etc
      # - specify arg1=Keyword,arg2=Qualifier-present,arg2=Qualifier-absent
      # &\,$=/>-<:(.');_*[|]"<--- arg4=punctuation to blank for word separation
      # abcdefghjklpqrsuALPRQ<--- equivalents, easier to enter, may enter "all"
      # Date=2019/06/17_11:52:30, Site=UV_Software, Host=uvsoft5, User=uvadm
      #===============================================================================
      #---> uvcopy table3d,fild1=tmp1,arg1=FROM
      table3d  2019/06/17_11:52:34  Counts by Targetword following specified Keyword
      tbl#0001  tblt1f7 e0(48)         argument
      line#   1strec#  %      count  target-word
          1         1  36         4   Customers
          2         1  18         2   EMPLOYEE
          3         1   9         1   Orders
          4         1  27         3   Patients
          5         1   9         1   STUDENT
                      100        11*  *TOTAL*

Now you can see that we are reporting TABLE-names found in all 8 SQL scripts (3 have Customers, 2 have EMPLOYEE,& others have only 1 TABLE-name in the script).

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4H1. Vancouver Utility Programming Aids - TableJobs

table2 - fixed position fields by displacement(length)

'table2' will build summary tables based on fixed position fields versus 'table3d' which searched records for a Key-Word & tabled the following Target-Word.

We will use $UV/demo/dat1/UScongress (US Government Representatives & Senators) to demonstrate table2. We downloaded an Excell file, extracted a csv file converted to a fixed column layout with uvcopy UScongresscsv2txt. You can see that uvcopy job at UVdemos.htm#10E1.

Here are samples of the 539 records with a column scale, since table2 expects arguments specified by record displacements & lengths (vs table3 which expected Key-Words to table following Target-Words).

samples US Reps & Senators .csv converted to fixed field

Abraham,Ralph,,,,Ralph Lee Abraham,1954-09-16,M,rep,LA,5,,Republican, Adams,Alma,,,,Alma S. Adams,1946-05-27,F,rep,NC,12,,Democrat, Aderholt,Robert,B.,,,Robert B. Aderholt,1965-07-22,M,rep,AL,4,,Republican,

 #        1         2         3         4         5         6         7         8
 #2345678901234567890123456789012345678901234567890123456789012345678901234567890
 001  Abraham             Ralph          19540916  M  LA  rep  Republican
 002  Adams               Alma           19460527  F  NC  rep  Democrat
 003  Aderholt            Robert         19650722  M  AL  rep  Republican
              ---------- omitting 533 lines ----------
 537  Young               Don            19330609  M  AK  rep  Republican
 538  Young               Todd           19720824  M  IN  sen  Republican
 539  Zeldin              Lee            19800130  M  NY  rep  Republican

table2 1st Example for discussion below


 uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
 ================================================================================
 # Report=/home/uvadm/demo/stats/UScongress_Party_a62b15
 # uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/UScongress
 # Options=a62b15   Title=Members by Party
 # Date=2019/07/05_16:25:17, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/05_16:25:17  Members by Party uop=a62b15
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  Party
     1         2  52       284  Democrat
     2       265   0         2  Independent
     3         1  46       253  Republican
                 100       539*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4H2. Vancouver Utility Programming Aids - TableJobs

table2 - Notes & Benefits

  1. 'table2' allows you to create table summaries for desired fields in your datafiles with fixed length fields - in contrast to the preceding 'table3' & 'table3d' which create table summaries based on key-words known to precede desired target-words.

  2. table2 will prompt for the file type, can be text (with LineFeeds), or fixed length records without LineFeeds, or variable (such as typ=RDW).

  3. Reports are created in subdir stats/... of your current working directory, so it is best to stay in the working directory with your datafile & stats sub-directories below you (ie - do not change into the datafile directories).

  4. Reports are automatically named from the datafilename, fieldnames,& field locations. For example in the sample report above the Report name (on 1st line of report) is: "Report=/home/uvadm/demo/stats/UScongress_Party_a62b15"

  5. This is convenient, you can run many reports without overwriting previous reports, and then use them to research problems or to assess the impacts of planned changes.

  6. The reports include all relevant information (inputs, fieldnames, locations, options, date/time, site, host,& userid).

  7. We recommend you specify all parameters on the command line, but you could wait & reply to the prompts for filename, field location displacement & length, fieldname,& options. Here is our sample comamnd with all parameters:

     uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
     ================================================================================
  1. Specifying all parameters on the command line makes it easy to use the command search history to recall, modify,& repeat desired commands.

  2. 'arg1=...' specifies the description for the heading above the table field-data & 'arg2=...' specifies an optional Title=... to be included in the report header. If specified on the command line, any spaces between words must be coded as '_' underscores & specify option 'u' on arg1u=... & arg2u=...

  3. Note that the main objective of these demos is to give you the knowledge to run these table utilities on your own data-files to achieve your objectives.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4H3. Vancouver Utility Programming Aids - TableJobs

table2 Execution & Console-Log

The sample above showed the command line & the report created, and now we will illustrate the console-log prompts for options, arguments, table fieldnames, & prompt to view output report.


 uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
 ================================================================================

Console Log - options & prompts

 uop=a0b0c0d0e0f0g0h0j0n0p0r0x1 - option defaults
 uop=a0                 - argument displacement (0 relative)
 uop=  b0               - argument length
 uop=    c0             - argument displacement (0 relative)
 uop=      d0           - argument length
 uop=        e0         - acum#1 dsplcmnt (omit c,d,e,f for counts only)
 uop=          f0       - acum#1 length
 uop=            g0     - acum#2 displacement (0 relative)
 uop=              h0   - acum#2 length
 uop=            j1     - edit all acums as qty (commas, no decimal)
 uop=            j2     - edit all acums as $ (commas & decimal point)
 uop=            j__    - table formats j1-j12 see uvcopy.doc tbf f1-f12
 uop=          n0       - argument n0=unpacked, n1=packed, n2=binary
 uop=        p0         - acum-fields p0=unpacked, p1=packed, p2=binary
 uop=          r8192    - input record size (max 8192)
 uop=               x1  - bypass any #comments in data file
 uop=              note - if file type LST specify r > largest possible
 uop=a30b6c38d6e53f9g3r64x1 <-- ENTER this for demo with dat1/sales3 file
 product#;quantity;amount   <-- ENTER this for demo Field Headings
 Summary of Qty & Amt by Product#  <-- ENTER this for demo Report Heading

User OPtion (uop) defaults = q1a0b0c0d0e0f0g0h0j0n0p0r0x1a62b15 -->null to accept or enter/override --> recsize (option r) unspcfd, default 2048 OK text files, may continue acum field#1 (option f) length unspcfd, may continue to count only enter argument & acum fieldnames with semicolon separators -->

 LST = typ default fili01 - null accept or enter new typ ?
 190705:174132:table2: EOF fili01 rds=539 size=38781: dat1/UScongress
 190705:174132:table2: EOF filo01 wrts=11 size=886: stats/UScongress_Party_a62b15
 EOJ, Output File written to: stats/UScongress_Party_a62b15
 default command = more, OR enter: vi,cat,more,lp,uvlp12,etc --> more <--
Note
  • table2 option prompts for command (optional) to view output report
  • you could reply 'more' to display (shortened version below)
 table2  2019/07/05_17:41:32  Members by Party uop=a62b15
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  Party
     1         2  52       284  Democrat
     2       265   0         2  Independent
     3         1  46       253  Republican
                 100       539*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4I1. Table2 Summaries of US Representatives & Senators

4 Table Summaries from dat1/UScongress

You can run various table summaries from the dat1/UScongress demo file. We will show a few of the shorter reports, followed by script sf/UScongress_stats, which runs 13 reports that we thought were interesting.

Here is a reminder of the UScongress file layout, in case you want to try making up your own commands to run table summaries on other field combinations.

 #        1         2         3         4         5         6         7         8
 #2345678901234567890123456789012345678901234567890123456789012345678901234567890
 001  Abraham             Ralph          19540916  M  LA  rep  Republican
 002  Adams               Alma           19460527  F  NC  rep  Democrat
 003  Aderholt            Robert         19650722  M  AL  rep  Republican

table US Represenatives/Senators by Party


 #1. uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
     ================================================================================
 # Report=/home/uvadm/demo/stats/UScongress_Party_a62b15
 # uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/UScongress
 # Options=a62b15   Title=Members by Party
 # Date=2019/07/05_16:25:17, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/05_16:25:17  Members by Party uop=a62b15
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  Party
     1         2  52       284  Democrat
     2       265   0         2  Independent
     3         1  46       253  Republican
                 100       539*  *TOTAL*

Notes

  1. You must enter the field displacement zero relative. In the above example the the options for Party field location are defined as 'uop=a62b15'. Option 'a62' defines the displacemnt & option 'b15' defines the length of the Party field.

  2. From the scale, you can see that the Party field begins in column 63 'one relative' which is 62 'zero relative'.

  3. The 1st column of the scale is '#' rather than '1', because option 'x1' allows records with '#' in column 1 to be bypassed (avoiding bad data in table). Allows you to add scales & other info in your data files if you so choose.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4I2. Table2 Summaries of US Representatives & Senators

table US Represenatives/Senators by House & Party


 #2. uvcopy table2,fili1=dat1/UScongress,uop=a57b3c62d15,arg1=House+Party,arg2u=Members_by_House+Party
     ==================================================================================================
 # Report=/home/uvadm/demo/stats/UScongress_House+Party_a57b3c62d15
 # uvcopy table2,fili1=dat1/UScongress,uop=a57b3c62d15,arg1=House+Party,arg2u=Members_by_House+Party
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/UScongress
 # Options=a57b20   Title=Members by House+Party
 # Date=2019/07/05_16:25:16, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/05_16:25:17  Members by House+Party uop=a57b20
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  House+Party
     1         2  44       239  rep  Democrat
     2         1  37       200  rep  Republican
     3        18   8        45  sen  Democrat
     4       265   0         2  sen  Independent
     5         5   9        53  sen  Republican
                 100       539*  *TOTAL*

Notes

  1. This example illustrates using 2 table arguments for House (sen or rep) & Party. Re: options 'uop=a57b3c62d15' - a57b3 specifies the House sen/rep in cols 58-60, a57 displacemnt & b3 length - c62d15 specifies the Party in columns 63-77, c62 displacment & d15 length

  2. arg1=House+Party specifies the field headings to appear directly above the data fields tabled on line 11 of the report. arg2u=Members_by_House+Party is an optional heading that will be inserted on line 9 of the report.

  3. We did not need to use option u on arg1 since no spaces in "House+Party", but did need option u on arg2u since we coded space with '_' underscores.

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4I3. Table2 Summaries of US Representatives & Senators

table US Represenatives/Senators by Party & Sex


 #3. uvcopy table2,fili1=dat1/UScongress,uop=a62b15c50d1,arg1=Party+Sex,arg2u=Members_by_Party+Sex
     =============================================================================================
 # Report=/home/uvadm/demo/stats/UScongress_Party+Sex_a62b15c50d1
 # uvcopy table2,fili1=dat1/UScongress,uop=a62b15c50d1,arg1=Party+Sex,arg2u=Members_by_Party+Sex
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/UScongress
 # Options=a62b15c50d1   Title=Members by Party+Sex
 # Date=2019/07/05_16:25:17, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/05_16:25:17  Members by Party+Sex uop=a62b15c50d1
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  Party+Sex
     1         2  20       108  Democrat        F
     2         4  32       176  Democrat        M
     3       265   0         2  Independent     M
     4         9   4        23  Republican      F
     5         1  42       230  Republican      M
                 100       539*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4I4. Table2 Summaries of US Representatives & Senators

table US Represenatives/Senators by State & Party


 #4. uvcopy table2,fili1=dat1/UScongress,uop=a53b2c62d15,arg1=State+Party,arg2u=Members_by_State+Party
     =================================================================================================
 # Report=/home/uvadm/demo/stats/UScongress_State+Party_a53b2c62d15
 # uvcopy table2,fili1=dat1/UScongress,uop=a53b2c62d15,arg1=State+Party,arg2u=Members_by_State+Party
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/UScongress
 # Options=a53b2c62d15   Title=Members by State+Party
 # Date=2019/07/05_16:25:22, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/05_16:25:22  Members by State+Party uop=a53b2c62d15
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  State+Party
     1       346   0         3  AK Republican
     2       246   0         2  AL Democrat
     3         3   1         7  AL Republican
     4        40   1         6  AR Republican
     5         9   0         1  AS Republican
         ------------- 83 lines omitted -------------
    89        18   0         4  WI Democrat
    90       141   1         6  WI Republican
    91       309   0         1  WV Democrat
    92        63   0         4  WV Republican
    93        21   0         3  WY Republican
                 100       539*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4J1. Table2 Summaries of US Representatives & Senators

script to run 13 table summaries on dat1/UScongress

 #!/bin/ksh
 # UScongress_stats - run table2 to create multi stats/reports from dat1/UScongress demo file
 #                  - by owen Townsend, UV Software, Jul04/2019
 # See details at http://uvsoftware.ca/tablejobs.htm or http://uvsoftware.ca/uvdemos.htm
 export UVCOPYROP=q0i63r0   # minimize console prompts
 uvcopy table2,fili1=dat1/UScongress,uop=a40b4,arg1=BirthYear,arg2u=Members_by_BirthYear
 uvcopy table2,fili1=dat1/UScongress,uop=a57b3,arg1=House,arg2u=Members_by_House
 uvcopy table2,fili1=dat1/UScongress,uop=a57b20,arg1=House+Party,arg2u=Members_by_House+Party
 uvcopy table2,fili1=dat1/UScongress,uop=a57b3c50d1,arg1=House+Sex,arg2u=Members_by_House+Sex
 uvcopy table2,fili1=dat1/UScongress,uop=a62b15,arg1=Party,arg2u=Members_by_Party
 uvcopy table2,fili1=dat1/UScongress,uop=a62b15c50d1,arg1=Party+Sex,arg2u=Members_by_Party+Sex
 uvcopy table2,fili1=dat1/UScongress,uop=a62b15c53d2,arg1=Party+State,arg2u=Members_by_Party+State
 uvcopy table2,fili1=dat1/UScongress,uop=a50b1,arg1=Sex,arg2u=Members_by_Sex
 uvcopy table2,fili1=dat1/UScongress,uop=a50b1c57d3,arg1=Sex+House,arg2u=Members_by_Sex+House
 uvcopy table2,fili1=dat1/UScongress,uop=a50b1c62d15,arg1=Sex+Party,arg2u=Members_by_Sex+Party
 uvcopy table2,fili1=dat1/UScongress,uop=a53b2,arg1=State,arg2u=Members_by_State
 uvcopy table2,fili1=dat1/UScongress,uop=a53b2c57d3,arg1=State+House,arg2u=Members_by_State+House
 uvcopy table2,fili1=dat1/UScongress,uop=a53b2c62d15,arg1=State+Party,arg2u=Members_by_State+Party
 echo "Output Reports in $PWD/stats - listed with line counts"
 llc stats
 #========
 File#   Lines
     1      69 stats/UScongress_BirthYear_a40b4
     2      15 stats/UScongress_House_a57b3
     3      18 stats/UScongress_House+Party_a57b3c62d15
     4      17 stats/UScongress_House+Sex_a57b3c50d1
     5      16 stats/UScongress_Party_a62b15
     6      18 stats/UScongress_Party+Sex_a62b15c50d1
     7     106 stats/UScongress_Party+State_a62b15c53d2
     8      15 stats/UScongress_Sex_a50b1
     9      17 stats/UScongress_Sex+House_a50b1c57d3
    10      18 stats/UScongress_Sex+Party_a50b1c62d15
    11      69 stats/UScongress_State_a53b2
    12     119 stats/UScongress_State+House_a53b2c57d3
    13     106 stats/UScongress_State+Party_a53b2c62d15
    13     603 total lines in    13 files in directory stats

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4K1. Vancouver Utility Programming Aids - TableJobs

Table2 Summaries of Canadian Members of Parliament

'table2' will build summary tables based on fixed position fields versus 'table3d' which searched records for a Key-Word & tabled the following Target-Word.

We will use $UV/demo/dat1/CanadaMPs (CAnadian Members of Parliament) to demo table2. We downloaded a spreadsheet, extracted a csv file (dat1/CanadaMPs.csv) & converted to a fixed layout so we could define specific fields required for some selections. See UVdemos.htm#10D1 if interested to see the uvcopy job (demo/pf/CanadaMPscsv2txt).

Here are the samples of the 335 records with a column scale to help you determine field locations. You must specify zero relative displacement with option 'a' and length with option 'b'. For example, use 'uop=a70b2' for the Province in columns 71-72.

samples of Canadian MPs .csv converted to fixed fields

Title,First Name,Last Name,Constituency,Province/Territory,Political Affiliation,Start Date ,Ziad,Aboultaif,Edmonton Manning,Alberta,Conservative,10/19/2015 ,Dan,Albas,Central Okanagan/Similkameen/Nicola,British Columbia,Conservative,10/19/2015

          1         2         3         4         5         6         7         8         9       100
 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
               ----------------- First 2 of 335 MP's ------------------
 001  Aboultaif          Ziad            Edmonton Manning              AB Conservative     2015
 002  Albas              Dan             Central Okanagan/Similkameen/ BC Conservative     2015
               --------------- samples of all parties --------------
 010  Anderson           David           Cypress Hills/Grasslands      SK Conservative     2015
 011  Angus              Charlie         Timmins/James Bay             ON NDP              2015
 023  Barsalou-Duval     Xavier          Pierre-Boucher/Les Patriotes/ QC Bloc Quebecois   2015
 032  Bernier            Maxime          Beauce                        QC People's Party   2015
 202  May                Elizabeth       Saanich/Gulf Islands          BC Green Party      2015
 328  Wilson-Raybould    Jody            Vancouver Granville           BC Independent      2015
               ----------------- Last 2 of 335 MP's ------------------
 334  Zahid              Salma           Scarborough Centre            ON Liberal          2015
 335  Zimmer             Bob             Prince George/Peace River/Nor BC Conservative     2015

We will build 3 tables: by Province cols 71-72, Party cols 74-88,& YearElected cols 91-94

Here are the 3 commands we will use, see results on the followng pages:


 #1. uvcopy table2,fili1=dat1/CanadaMPs,uop=a70b2,arg1=Province,arg2u=CDN_MPs_by_Province
     ====================================================================================
 #2. uvcopy table2,fili1=dat1/CanadaMPs,uop=a73b17,arg1=Party,arg2u=CDN_MPs_by_Party
     ===============================================================================
 #3. uvcopy table2,fili1=dat1/CanadaMPs,uop=a90b4,arg1=YearElected,arg2u=CDN_MPs_by_YearElected
     ==========================================================================================

Notes

  1. We enter all arguments on the comamnd line, but you could omit & enter at the prompts

  2. For spaces within arguments, you must code them as '_' undercores & use option 'u' - for example 'arg3u=CDN_MPs_by_Province'

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4K2. Table2 Summaries of Canadian Members of Parliament

3 Table Summaries from dat1/CanadaMPs


 #1. uvcopy table2,fili1=dat1/CanadaMPs,uop=a70b2,arg1=Province,arg2u=CDN_MPs_by_Province
     ====================================================================================

table CA MPs by Province

 # Report=/home/uvadm/demo/stats/CanadaMPs_Province_a70b2
 # uvcopy table2,fili1=dat1/CanadaMPs,uop=a70b2,arg1=Province,arg2u=CDN_MPs_by_Province
 # ====================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/CanadaMPs
 # Options=a70b2   Title=CDN MPs by Province
 # Date=2019/07/06_11:42:06, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/06_11:42:08  CDN MPs by Province uop=a70b2
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  Province
     1         1  10        34  AB
     2         2  12        41  BC
     3        15   4        14  MB
     4        13   2        10  NB
     5       147   2         7  NL
     6        59   2        10  NS
     7       212   0         1  NT
     8       310   0         1  NU
     9         3  36       121  ON
    10        58   1         4  PE
    11         8  22        77  QC
    12        10   4        14  SK
    13        19   0         1  YT
                 100       335*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4K3. Table2 Summaries of Canadian Members of Parliament

table CA MPs by House & Party


 #2. uvcopy table2,fili1=dat1/CanadaMPs,uop=a73b17,arg1=Party,arg2u=CDN_MPs_by_Party
     ===============================================================================
 # Report=/home/uvadm/demo/stats/CanadaMPs_Party_a73b17
 # uvcopy table2,fili1=dat1/CanadaMPs,uop=a73b17,arg1=Party,arg2u=CDN_MPs_by_Party
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/CanadaMPs
 # Options=a73b17   Title=CDN MPs by Party
 # Date=2019/07/06_11:42:08, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/06_11:42:09  CDN MPs by Party uop=a73b17
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  Party
     1        23   2        10  Bloc Quebecois
     2       325   0         1  Co-operative Co
     3         1  28        96  Conservative
     4       195   0         2  Green Party
     5        52   2         7  Independent
     6         4  52       177  Liberal
     7        11  12        41  NDP
     8        32   0         1  People's Party
                 100       335*  *TOTAL*

table CA MPs by Year-Elected


 #3. uvcopy table2,fili1=dat1/CanadaMPs,uop=a90b4,arg1=YearElected,arg2u=CDN_MPs_by_YearElected
     ==========================================================================================
 # Report=/home/uvadm/demo/stats/CanadaMPs_YearElected_a90b4
 # uvcopy table2,fili1=dat1/CanadaMPs,uop=a90b4,arg1=YearElected,arg2u=CDN_MPs_by_YearElected
 # ===================================================================================
 # - read a file, building a table of counts & values by 1 or 2 arguments, dump at EOF
 # InPutFile=dat1/CanadaMPs
 # Options=a90b4   Title=CDN MPs by YearElected
 # Date=2019/07/06_11:42:09, Site=UV_Software, Host=uvsoft5, User=uvadm
 #====================================================================================
 table2  2019/07/06_11:42:10  CDN MPs by YearElected uop=a90b4
 tbl#0001  tblo1t1  n0($rm48)   <----- argument ----->
 line#   1strec#  %      count  YearElected
     1         1  94       317  2015
     2       222   0         1  2016
     3        30   3        11  2017
     4        22   0         2  2018
     5        27   1         4  2019
                 100       335*  *TOTAL*

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

4K4. Table2 Summaries of Canadian Members of Parliament

script to run multiple stats on dat1/CanadaMPs

 #!/bin/ksh
 # CandaMPs_stats - run table2 to create multi stats/reports from dat1/CanadaMPs demo file
 #                - by owen Townsend, UV Software, Jul06/2019
 # See details at http://uvsoftware.ca/tablejobs.htm or http://uvsoftware.ca/uvdemos.htm
 export UVCOPYROP=q0i63r0   # minimize console prompts
 #
 uvcopy table2,fili1=dat1/CanadaMPs,uop=a70b2,arg1=Province,arg2u=CDN_MPs_by_Province
 #==================================================================================
 #
 uvcopy table2,fili1=dat1/CanadaMPs,uop=a73b17,arg1=Party,arg2u=CDN_MPs_by_Party
 #==============================================================================
 #
 uvcopy table2,fili1=dat1/CanadaMPs,uop=a90b4,arg1=YearElected,arg2u=CDN_MPs_by_YearElected
 #=========================================================================================
 echo "Output Reports in $PWD/stats/... - listed below with line counts"
 llc stats
 #========
 File#   Lines
     1      21 demo/stats/CanadaMPs_Party_a73b17
     2      26 demo/stats/CanadaMPs_Province_a70b2
     3      18 demo/stats/CanadaMPs_YearElected_a90b4
     4      65 total lines in    3 files in directory demo/stats

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

Permuted Index of Keywords from ** Headings **

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 

4B3 $home: copy $UV/demo to your $HOME/demo/...
4B3 $uv: copy $UV/demo to your $HOME/demo/...

4A1 ...: table3d report created in stats/...
4B3 ...: copy $UV/demo to your $HOME/demo/...
4H1 .csv: samples US Reps & Senators .csv converted to fixed field
4K1 .csv: samples of Canadian MPs .csv converted to fixed fields

4E2 Arguments: Notes re arguments & options
4G3 Attempt: table3d summarize SQL TABLEs - 1st attempt

4H1 Below: table2 1st Example for discussion below
4A1 Benefits: Notes, Benefits of table3d reports
4H2 Benefits: table2 - Notes & Benefits

4K2 Canadamps: 3 Table Summaries from dat1/CanadaMPs
4K4 Canadamps: script to run multiple stats on dat1/CanadaMPs
4K1 Canadian: Table2 Summaries of Canadian Members of Parliament
4K1 Canadian: samples of Canadian MPs .csv converted to fixed fields
4B1 Check-list: Setup check-list - Ready to run TableJob test/demos ?
4G4 Combine: Solution - combine SQL statements onto 1 line
4G5 Combine: Problem Solution - Combine SELECT stmnts onto 1 line
4G5 Combined: rerun table3d inputting SELECT stmnts combined onto 1 line
4E2 Command: now execute the table3d command
4H3 Console: Console Log - options & prompts
4H3 Console-log: table2 Execution & Console-Log
4H1 Converte: samples US Reps & Senators .csv converted to fixed field
4K1 Converte: samples of Canadian MPs .csv converted to fixed fields
4B3 Copied: TableJobs demo files copied to your homedir
4B3 Copy: $UV/demo to your $HOME/demo/...
4G6 Correct: rerun table3d to create correct summary of SQL TABLEs

4I1 Dat1: 4 Table Summaries from dat1/UScongress
4J1 Dat1: script to run 13 table summaries on dat1/UScongress
4K2 Dat1: 3 Table Summaries from dat1/CanadaMPs
4K4 Dat1: script to run multiple stats on dat1/CanadaMPs
4B3 Demo: TableJobs demo files copied to your homedir
4B3 Demo: copy $UV/demo to your $HOME/demo/...
4B3 Demo: copy $UV/demo to your $HOME/demo/...
4F2 Demo: tableIP - simplified table summary demo
4F3 Demo: Notes re uvcopy demo job 'tableIP'
4B1 Demos: Setup check-list - Ready to run TableJob test/demos ?
4H1 Discussion: table2 1st Example for discussion below
4H1 Displacement: table2 - fixed position fields by displacement(length)

4A1 Example: Example#1 - table summarize all IP#s in FTP scripts
4D1 Example: Example#2 - option 'w#' TargetWord offset from KeyWord
4D2 Example: Example#3 - option 'y' no of words to table
4H1 Example: table2 1st Example for discussion below
4E2 Execute: now execute the table3d command
4H3 Execution: table2 Execution & Console-Log

4H1 Field: samples US Reps & Senators .csv converted to fixed field
4H1 Fields: table2 - fixed position fields by displacement(length)
4K1 Fields: samples of Canadian MPs .csv converted to fixed fields
4H1 Fixed: samples US Reps & Senators .csv converted to fixed field
4H1 Fixed: table2 - fixed position fields by displacement(length)
4K1 Fixed: samples of Canadian MPs .csv converted to fixed fields
4A1 Ftp: Example#1 - table summarize all IP#s in FTP scripts

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 

4E2 General: General Purpose Utilities

4B3 Homedir: TableJobs demo files copied to your homedir
4I2 House: table US Represenatives/Senators by House & Party
4K3 House: table CA MPs by House & Party

4G5 Inputting: rerun table3d inputting SELECT stmnts combined onto 1 line

4D1 Keyword: Example#2 - option 'w#' TargetWord offset from KeyWord

4G4 Line: Solution - combine SQL statements onto 1 line
4G5 Line: Problem Solution - Combine SELECT stmnts onto 1 line
4G5 Line: rerun table3d inputting SELECT stmnts combined onto 1 line
4G2 Listed: SQL scripts listed - test files for table3d
4H3 Log: Console Log - options & prompts

4K1 Member: Table2 Summaries of Canadian Members of Parliament
4G4 Missing: PROBLEM - some TABLE names missing ?
4D1 Most: useful options - option 'w#'
4D2 Most: useful options - option 'y#'
4K2 Mp: table CA MPs by Province
4K3 Mp: table CA MPs by House & Party
4K3 Mp: table CA MPs by Year-Elected
4K1 Mps: samples of Canadian MPs .csv converted to fixed fields

4E2 Now: execute the table3d command

4D2 Of: Example#3 - option 'y' no of words to table
4D1 Offset: Example#2 - option 'w#' TargetWord offset from KeyWord
4G4 Onto: Solution - combine SQL statements onto 1 line
4G5 Onto: Problem Solution - Combine SELECT stmnts onto 1 line
4G5 Onto: rerun table3d inputting SELECT stmnts combined onto 1 line
4D1 Option: Example#2 - option 'w#' TargetWord offset from KeyWord
4D1 Option: most useful options - option 'w#'
4D2 Option: Example#3 - option 'y' no of words to table
4D2 Option: most useful options - option 'y#'
4C1 Options: table3d options
4D1 Options: most useful options - option 'w#'
4D2 Options: most useful options - option 'y#'
4E2 Options: Notes re arguments & options
4H3 Options: Console Log - options & prompts

4K1 Parliament: Table2 Summaries of Canadian Members of Parliament
4I1 Party: table US Represenatives/Senators by Party
4I2 Party: table US Represenatives/Senators by House & Party
4I3 Party: table US Represenatives/Senators by Party & Sex
4I4 Party: table US Represenatives/Senators by State & Party
4K3 Party: table CA MPs by House & Party
4E2 Pgm: table summarize PGM names in JCL
4H1 Position: table2 - fixed position fields by displacement(length)
4F1 Pre-programmed: Writing specific uvcopy jobs vs Pre-Programmed TableJobs
4G1 Problem: PROBLEM in SQL scripts for table3d
4G4 Problem: PROBLEM - some TABLE names missing ?
4G5 Problem: Problem Solution - Combine SELECT stmnts onto 1 line
4E1 Program: table3d - create summary table of programs in JCL
4H3 Prompts: Console Log - options & prompts
4K2 Province: table CA MPs by Province

4B2 Relevant: Vancouver Utilities - subdirs relevant to TableJobs
4I1 Represenatives: table US Represenatives/Senators by Party
4I2 Represenatives: table US Represenatives/Senators by House & Party
4I3 Represenatives: table US Represenatives/Senators by Party & Sex
4I4 Represenatives: table US Represenatives/Senators by State & Party
4H1 Reps: samples US Reps & Senators .csv converted to fixed field
4G5 Rerun: table3d inputting SELECT stmnts combined onto 1 line
4G6 Rerun: table3d to create correct summary of SQL TABLEs
4B1 Run: Setup check-list - Ready to run TableJob test/demos ?
4J1 Run: script to run 13 table summaries on dat1/UScongress
4K4 Run: script to run multiple stats on dat1/CanadaMPs

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 

4H1 Samples: US Reps & Senators .csv converted to fixed field
4K1 Samples: of Canadian MPs .csv converted to fixed fields
4G5 Select: Problem Solution - Combine SELECT stmnts onto 1 line
4G5 Select: rerun table3d inputting SELECT stmnts combined onto 1 line
4I1 Senator: table US Represenatives/Senators by Party
4I2 Senator: table US Represenatives/Senators by House & Party
4I3 Senator: table US Represenatives/Senators by Party & Sex
4I4 Senator: table US Represenatives/Senators by State & Party
4H1 Senators: samples US Reps & Senators .csv converted to fixed field
4B1 Setup: Setup check-list - Ready to run TableJob test/demos ?
4I3 Sex: table US Represenatives/Senators by Party & Sex
4F2 Simplified: tableIP - simplified table summary demo
4G4 Solution: Solution - combine SQL statements onto 1 line
4G5 Solution: Problem Solution - Combine SELECT stmnts onto 1 line
4G4 Some: PROBLEM - some TABLE names missing ?
4F1 Specific: Writing specific uvcopy jobs vs Pre-Programmed TableJobs
4G1 Sql: PROBLEM in SQL scripts for table3d
4G1 Sql: table3d - create summaries of TABLEs in SQL scripts
4G2 Sql: SQL scripts listed - test files for table3d
4G3 Sql: table3d summarize SQL TABLEs - 1st attempt
4G4 Sql: Solution - combine SQL statements onto 1 line
4G6 Sql: rerun table3d to create correct summary of SQL TABLEs
4K4 Stat: script to run multiple stats on dat1/CanadaMPs
4I4 State: table US Represenatives/Senators by State & Party
4G4 Statements: Solution - combine SQL statements onto 1 line
4A1 Stats: table3d report created in stats/...
4G5 Stmnts: Problem Solution - Combine SELECT stmnts onto 1 line
4G5 Stmnts: rerun table3d inputting SELECT stmnts combined onto 1 line
4B2 Subdirs: Vancouver Utilities - subdirs relevant to TableJobs
4G6 Summar: rerun table3d to create correct summary of SQL TABLEs
4J1 Summarie: script to run 13 table summaries on dat1/UScongress
4G1 Summaries: table3d - create summaries of TABLEs in SQL scripts
4I1 Summaries: 4 Table Summaries from dat1/UScongress
4K1 Summaries: Table2 Summaries of Canadian Members of Parliament
4K2 Summaries: 3 Table Summaries from dat1/CanadaMPs
4A1 Summarize: Example#1 - table summarize all IP#s in FTP scripts
4E2 Summarize: table summarize PGM names in JCL
4G3 Summarize: table3d summarize SQL TABLEs - 1st attempt
4E1 Summary: table3d - create summary table of programs in JCL
4F2 Summary: tableIP - simplified table summary demo

4A1 Table: Example#1 - table summarize all IP#s in FTP scripts
4D2 Table: Example#3 - option 'y' no of words to table
4E1 Table: table3d - create summary table of programs in JCL
4E2 Table: summarize PGM names in JCL
4F2 Table: tableIP - simplified table summary demo
4G1 Table: table3d - create summaries of TABLEs in SQL scripts
4G4 Table: PROBLEM - some TABLE names missing ?
4I1 Table: 4 Table Summaries from dat1/UScongress
4I1 Table: US Represenatives/Senators by Party
4I2 Table: US Represenatives/Senators by House & Party
4I3 Table: US Represenatives/Senators by Party & Sex
4I4 Table: US Represenatives/Senators by State & Party
4J1 Table: script to run 13 table summaries on dat1/UScongress
4K2 Table: 3 Table Summaries from dat1/CanadaMPs
4K2 Table: CA MPs by Province
4K3 Table: CA MPs by House & Party
4K3 Table: CA MPs by Year-Elected
4H1 Table2: - fixed position fields by displacement(length)
4H1 Table2: 1st Example for discussion below
4H2 Table2: - Notes & Benefits
4H3 Table2: Execution & Console-Log
4K1 Table2: Table2 Summaries of Canadian Members of Parliament
4A1 Table3d: Notes, Benefits of table3d reports
4A1 Table3d: report created in stats/...
4C1 Table3d: options
4E1 Table3d: - create summary table of programs in JCL
4E2 Table3d: now execute the table3d command
4G1 Table3d: PROBLEM in SQL scripts for table3d
4G1 Table3d: - create summaries of TABLEs in SQL scripts
4G2 Table3d: SQL scripts listed - test files for table3d
4G3 Table3d: summarize SQL TABLEs - 1st attempt
4G5 Table3d: rerun table3d inputting SELECT stmnts combined onto 1 line
4G6 Table3d: rerun table3d to create correct summary of SQL TABLEs
4F2 Tableip: tableIP - simplified table summary demo
4F3 Tableip: Notes re uvcopy demo job 'tableIP'
4B1 Tablejob: Setup check-list - Ready to run TableJob test/demos ?
4B2 Tablejobs: Vancouver Utilities - subdirs relevant to TableJobs
4B3 Tablejobs: TableJobs demo files copied to your homedir
4F1 Tablejobs: Writing specific uvcopy jobs vs Pre-Programmed TableJobs
4G3 Tables: table3d summarize SQL TABLEs - 1st attempt
4G6 Tables: rerun table3d to create correct summary of SQL TABLEs
4D1 Targetword: Example#2 - option 'w#' TargetWord offset from KeyWord
4B1 Test: Setup check-list - Ready to run TableJob test/demos ?
4G2 Test: SQL scripts listed - test files for table3d
4B1 Testdata: file contents

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 

4I1 Uscongress: 4 Table Summaries from dat1/UScongress
4J1 Uscongress: script to run 13 table summaries on dat1/UScongress
4D1 Useful: most useful options - option 'w#'
4D2 Useful: most useful options - option 'y#'
4B2 Utilities: Vancouver Utilities - subdirs relevant to TableJobs
4E2 Utilities: General Purpose Utilities
4F1 Uvcopy: Writing specific uvcopy jobs vs Pre-Programmed TableJobs
4F3 Uvcopy: Notes re uvcopy demo job 'tableIP'

4B2 Vancouver: Vancouver Utilities - subdirs relevant to TableJobs

4D2 Word: Example#3 - option 'y' no of words to table
4F1 Writing: Writing specific uvcopy jobs vs Pre-Programmed TableJobs

4K3 Year-elected: table CA MPs by Year-Elected

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 

Goto:   Begin this doc End this doc Index this doc Contents this library UVSI Home-Page

Visitor Counters for ThisYear and LastYear