Modifying/combining two text outputs

UNIX for Beginners Q & A
1

greetings,

I have what seems to be a fairly complex task at hand. I am dealing with 3 outputs from a database.

a) Listing of tables. The first 3 characters are always an identifier of an application. See in part b -

Example-

select tablename from system.tables
ABC1
ABC2
RFV1

b) Each table has a field I need to capture-

select docname from ABC1
DOCUMENT1
DOCUMENT2
DOCUMENT3

c) The first three characters of that table name, is always the 3 digit identifier of a specific application..

select identifier, actualname from application
ABC,DataDriven

SO- What I need to do is combine everything into a distinct CSV file, where I have something like this

(docnameFromABC1),(actualName)
DOCUMENT1, DataDriven
DOCUMENT2, DataDriven

My problem is that I can't join the tables on a certain field. Can anyone think of a fancy way i may be able to use a where clause, or a like clause, or something..or do some fancy awk work on this?

2

Hi jeffs42885,
You've been with us for well over eight year now. You know we always want to know what operating system and shell you're using, what you have tried to solve this problem on your own, what you have done that is working well, what you have done that isn't getting the output you want, and where you are stuck?

Please give us the above details. Please help us help you.

3

sorry about that. This is AIX/DB2 using KSH.

I have tried various joins on fields that I thought were similar..and other queries.

I did this to get a listing of the application ID's based on the table names-

select substr(tabname,1,3) from syscat.tables where LENGTH(tabname) = 4 and tabschema='ROOT'

I am stuck here. This is where my skill runs out sadly. I have ideas, just not sure how to implement them, or even how to put them into words!

--- Post updated at 06:27 PM ---

Here's some progress, I think

This output gives me about 90 or so rows, probably more in production that I will need to use below. Snippet-

db2 => select syscat.tables.tabname from syscat.tables where length(tabname)=4 and tabschema='ROOT'

TABNAME
--------------------------------------------------------------------------------------------------------------------------------
ACA1
AKA1
AZZ1
BHY1
BJA1
....

The column header isn't correct but the results are when I manually did a count from AKA1 and ACA1.

db2 => select distinct DOCUMENT, 'ACA1' as ID from ACA1 union all select distinct DOCUMENT, 'AKA1' as AKA1 from AKA1

DOCUMENT    ACA1
----------- ----
15FAAA      AKA1
31FAAA      AKA1
31FAAZ      AKA1
2FAAA       ACA1

My next step, is to figure out how to add another column or field to that.

Basically, if column2(AKA1) like "%something" aka (AKA) - add a field next to it. Use an output file to create it. That file looks like this...

AKA, Program1
ACA, Program2
IED, Program3

So, desired output-

15FAAA, AKA1, Program1
31FAAA, AKA1, Program1
31FAAZ, AKA1, Program1
2FAAA, ACA1, Program2

I hope I am making somewhat sense :slight_smile: