-1

I need to list the table names if it finds particular pattern from the below file.

CREATE TABLE IMS.ACTIVITY (
        "ID" INTEGER NOT NULL ,
        OWNING_TYPE FOR COLUMN OWNIN00001 VARCHAR(20) CCSID 37 NOT NULL ,
        OWNING_ID INTEGER NOT NULL ,
        ACTIVITY_TYPE FOR COLUMN ACTIV00001 VARCHAR(50) CCSID 37 NOT NULL ,
        CAMPAIGN_TYPE FOR COLUMN CAMPA00001 VARCHAR(50) CCSID 37 NOT NULL ,
        AUTHOR VARCHAR(100) CCSID 37 NOT NULL ,
        COMPLETED TIMESTAMP NOT NULL ,
        "COMMENT" VARCHAR(1000) CCSID 37 DEFAULT NULL ,
        CUR_LOCN_STATUS FOR COLUMN CUR_L00001 VARCHAR(20) CCSID 37 DEFAULT NULL ,
        FOLLOWUP_DATE FOR COLUMN FOLLO00001 DATE DEFAULT NULL ,
        FOLLOWUP_EMAIL FOR COLUMN FOLLO00002 VARCHAR(300) CCSID 37 DEFAULT NULL ,
        CREATETIMESTAMP FOR COLUMN CRTTIME    TIMESTAMP DEFAULT NULL ,
        CONSTRAINT IMS.QSYS_ACTIVITY_00001 PRIMARY KEY( "ID" ) )
        ;

CREATE TABLE IMS.ADDRESS (
        "ID" INTEGER NOT NULL ,
        PROVINCESTATE FOR COLUMN PROVI00001 VARCHAR(30) CCSID 37 DEFAULT NULL ,
        CITY VARCHAR(30) CCSID 37 DEFAULT NULL ,
        COUNTRY VARCHAR(30) CCSID 37 DEFAULT NULL ,
        ADDRESSLINE1 FOR COLUMN ADDRE00001 VARCHAR(30) CCSID 37 DEFAULT NULL ,
        ADDRESSLINE2 FOR COLUMN ADDRE00002 VARCHAR(30) CCSID 37 DEFAULT NULL ,
        POSTALCODE VARCHAR(30) CCSID 37 DEFAULT NULL ,
        CONSTRAINT IMS.QSYS_ADDRESS_00001 PRIMARY KEY( "ID" ) )
        ;

From the above file if we search for CCSID It should list the line that contains CCSID and the CREATE TABLE line for respective CCSIDs.

The output should be like

CREATE TABLE IMS.ACTIVITY
OWNING_TYPE FOR COLUMN OWNIN00001 VARCHAR(20) CCSID 37 NOT NULL
ACTIVITY_TYPE FOR COLUMN ACTIV00001 VARCHAR(50) CCSID 37 NOT NULL
CAMPAIGN_TYPE FOR COLUMN CAMPA00001 VARCHAR(50) CCSID 37 NOT NULL
AUTHOR VARCHAR(100) CCSID 37 NOT NULL
"COMMENT" VARCHAR(1000) CCSID 37 DEFAULT NULL
CUR_LOCN_STATUS FOR COLUMN CUR_L00001 VARCHAR(20) CCSID 37 DEFAULT NULL
FOLLOWUP_EMAIL FOR COLUMN FOLLO00002 VARCHAR(300) CCSID 37 DEFAULT NULL

print lines containing "table" and its associated "CCSID" if present within () in the paragraphs below.

  • Please edit your question to include example output. – jasonwryan Sep 16 '15 at 6:01
  • What determines the output? Please make some effort to clarify what it is you are asking... – jasonwryan Sep 16 '15 at 6:09
2

There may be more than one way to do this, but I would:

  1. Use tr to remove all new lines, making the whole file one long line.
  2. Replace ; with ;\n which puts 1 line per query
  3. Grep for the query(s) you are trying to find
  4. reformat results as desired

Here's a one liner that should work:

cat filename|tr -d '\n'|sed -e 's/;/;\n/g'|grep CCSID|sed -e 's/,/,\n/g'

To only include the create table and CCSID columns in your output:

cat filename|tr -d '\n'|sed -e 's/;/;\n/g'|grep CCSID|sed -e 's/,/,\n/g'|grep "CCSID\|CREATE TABLE"

Correction, with the second command you'll end up with misformatted lines, here's an updated one(kinda long, but I tested it against the data you provided mixed with modified data with DDSID which was filtered out):

cat testfile|tr -d '\n'|sed -e 's/;/;\n/g'|grep CCSID|sed -e 's/,/,\n/g'|grep "CCSID\|CREATE TABLE"|tr -d '\n'|sed -e 's/CREATE TABLE/;CREATE TABLE/g'|sed -e 's/,$/);/g'|sed -e 's/^;//g'|sed -e 's/,;/);/g'|sed -e 's/;/;\n/g'|sed -e 's/,/,\n/g'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.