mjhjmtu

I am a DBA and have the output from a reorg generation process that gives me DML in the following format :

REORG TABLE SCHEMA.A some other options
REORG TABLE SCHEMA.B some other options
REORG TABLE SCHEMA.C some other options
REORG TABLE SCHEMA.D some other options
REORG INDEXES ALL FOR TABLE SCHEMA.A some other options
REORG INDEXES ALL FOR TABLE SCHEMA.B some other options
REORG INDEXES ALL FOR TABLE SCHEMA.C some other options
REORG INDEXES ALL FOR TABLE SCHEMA.D some other options
RUNSTATS ON TABLE SCHEMA.A some other options
RUNSTATS ON TABLE SCHEMA.B some other options
RUNSTATS ON TABLE SCHEMA.C some other options
RUNSTATS ON TABLE SCHEMA.D some other options

For reasons I won't go in to here, i need this file sorted into the following order (note there might be less or more than 4 tables, or less or more than 3 entries per table):

REORG TABLE SCHEMA.A
REORG INDEXES ALL FOR TABLE SCHEMA.A
RUNSTATS ON TABLE SCHEMA.A
REORG TABLE SCHEMA.B
REORG INDEXES ALL FOR TABLE SCHEMA.B
RUNSTATS ON TABLE SCHEMA.B
...etc

ie. REORG TABLES, REORG INDEX then RUNSTAT for each table in the file.

I have tried the following (plus variants):

sort -t. -k2 test.fil
sort -t. -k2.1,2.8 -k1.1,1.7 test.fil

but the issue seems to be getting the secondary sorting (REORG TABLE, REORG INDEX then RUNSTAT) working correctly.

The first sort command above gets objects in order of descending table name, then sorted by length of k2.

The second sort command doesn't account for the fact that TABLE comes after INDEX (T after I) in the alphabet, but that I need INDEX after Table.

Any gurus out there that can help, muchly appreciated. Maybe there's other awk's and linux commands to use in conjunction?

I've also been through the sort command options, maybe there's some i've missed.

Let's make an awk script that modifies your data so that it's easily sortable the way you want it to be sorted:

BEGIN OFS = "t" # set output delimiter to a tab

/REORG TABLE/ $0 = 1 OFS $0 # prefix line with 1 for this op.
/REORG INDEX/ $0 = 2 OFS $0 # with 2
/RUNSTATS/ $0 = 3 OFS $0 # with 3

# prefix line with schema name (unconditionally)
 match($0,"SCHEMA.[^ ]*"); $0 = substr($0,RSTART,RLENGTH) OFS $0 

# output modified line
 print 

This awk script would prepend each line with two tab-separated values:

1. The schema name, e.g. SCHEMA.A.


2. The step that this operation happens in for this schema, which is an integer, 1, 2 or 3.

Running it on your data:

$ awk -f script.awk file
SCHEMA.A 1 REORG TABLE SCHEMA.A some other options
SCHEMA.B 1 REORG TABLE SCHEMA.B some other options
SCHEMA.C 1 REORG TABLE SCHEMA.C some other options
SCHEMA.D 1 REORG TABLE SCHEMA.D some other options
SCHEMA.A 2 REORG INDEXES ALL FOR TABLE SCHEMA.A some other options
SCHEMA.B 2 REORG INDEXES ALL FOR TABLE SCHEMA.B some other options
SCHEMA.C 2 REORG INDEXES ALL FOR TABLE SCHEMA.C some other options
SCHEMA.D 2 REORG INDEXES ALL FOR TABLE SCHEMA.D some other options
SCHEMA.A 3 RUNSTATS ON TABLE SCHEMA.A some other options
SCHEMA.B 3 RUNSTATS ON TABLE SCHEMA.B some other options
SCHEMA.C 3 RUNSTATS ON TABLE SCHEMA.C some other options
SCHEMA.D 3 RUNSTATS ON TABLE SCHEMA.D some other options

What this does is making the sorting keys easier to find for sort.

This can then be sorted and the new columns may be deleted:

$ awk -f script.awk file | sort | cut -f 3-
REORG TABLE SCHEMA.A some other options
REORG INDEXES ALL FOR TABLE SCHEMA.A some other options
RUNSTATS ON TABLE SCHEMA.A some other options
REORG TABLE SCHEMA.B some other options
REORG INDEXES ALL FOR TABLE SCHEMA.B some other options
RUNSTATS ON TABLE SCHEMA.B some other options
REORG TABLE SCHEMA.C some other options
REORG INDEXES ALL FOR TABLE SCHEMA.C some other options
RUNSTATS ON TABLE SCHEMA.C some other options
REORG TABLE SCHEMA.D some other options
REORG INDEXES ALL FOR TABLE SCHEMA.D some other options
RUNSTATS ON TABLE SCHEMA.D some other options