CODEWITHSUNDEEP
bashawksed
BennyMinVA
BennyMinVA

Reputation: 35

AWK - Stripping Out lines of DDL

I have the following file:

CREATE TABLE "DB2INST1 "."EMAIL_ADDRESS"  (
                  "EMAIL_ADDRESS_ID" INTEGER NOT NULL ,
                  "PERSON_ID" INTEGER ,
                  "EMAIL_ADDRESS" VARCHAR(128) NOT NULL ,
                  "CC_ADDRESS" VARCHAR(128) ,
                  "UPPER_EMAIL_ADDRESS" VARCHAR(128) GENERATED ALWAYS AS (UPPER(EMAIL_ADDRESS)) ,
                 IN "USERSPACE5" INDEX IN "IDXSPACE5" LONG IN "LONGSPCE1" ;


CREATE TABLE "DB2INST "."FIELD_RESPONSE"  (
                  "FIELD_RESPONSE_ID" INTEGER NOT NULL ,
                  "CUSTOM_FIELD_ID" INTEGER ,
                  "NAME" VARCHAR(100) NOT NULL ,
                  "RESPONSE" VARCHAR(100) ,
                  "RESPONSE_LONG" CLOB(256000) LOGGED NOT COMPACT ,
                  "FIELD_RESPONSE_100" VARCHAR(100) GENERATED ALWAYS AS (case when RESPONSE is null or RESPONSE = '' then cast(RESPONSE_LONG as varchar(100)) else RESPONSE end) ,
                 COMPRESS YES
                 DATA CAPTURE CHANGES
                 IN "USERSPACE1" INDEX IN "IDXSPACE1" LONG IN "LONGSPCE1" ;

I am able to strip out each of the these records out of the DDL using the following AWK command:

awk '/^CREATE TABLE/ {print}' FS="\n" RS="" < src.ddl > tables.ddl

Now, I am trying to remove the GENERATED ALWAYS and everything after that. I would like the file to look like this (END RESULT):

CREATE TABLE "DB2INST1 "."EMAIL_ADDRESS"  (
                  "EMAIL_ADDRESS_ID" INTEGER NOT NULL ,
                  "PERSON_ID" INTEGER ,
                  "EMAIL_ADDRESS" VARCHAR(128) NOT NULL ,
                  "CC_ADDRESS" VARCHAR(128) ,
                  "UPPER_EMAIL_ADDRESS" VARCHAR(128)  ,
                 IN "USERSPACE5" INDEX IN "IDXSPACE5" LONG IN "LONGSPCE1" ;


CREATE TABLE "DB2INST "."FIELD_RESPONSE"  (
                  "FIELD_RESPONSE_ID" INTEGER NOT NULL ,
                  "CUSTOM_FIELD_ID" INTEGER ,
                  "NAME" VARCHAR(100) NOT NULL ,
                  "RESPONSE" VARCHAR(100) ,
                  "RESPONSE_LONG" CLOB(256000) LOGGED NOT COMPACT ,
                  "FIELD_RESPONSE_100" VARCHAR(100)  ,
                 COMPRESS YES
                 DATA CAPTURE CHANGES
                 IN "USERSPACE1" INDEX IN "IDXSPACE1" LONG IN "LONGSPCE1" ;

I have tried to use this AWK in the 

|awk '{print $1 "  " $2 ", " }' < tables.ddl ...

However, that only prints out two columns of data.

Any suggestions?

Upvotes: 0

Views: 81

Answers (3)

konsolebox
konsolebox

Reputation: 75548

You can do it like this:

awk 'BEGIN{FS="\n"; RS=""}/^CREATE TABLE/{sub(/GENERATED ALWAYS.*,/, ","); print}' src.ddl > tables.ddl

Excpected output:

CREATE TABLE "DB2INST1 "."EMAIL_ADDRESS"  (
                  "EMAIL_ADDRESS_ID" INTEGER NOT NULL ,
                  "PERSON_ID" INTEGER ,
                  "EMAIL_ADDRESS" VARCHAR(128) NOT NULL ,
                  "CC_ADDRESS" VARCHAR(128) ,
                  "UPPER_EMAIL_ADDRESS" VARCHAR(128) ,
                 IN "USERSPACE5" INDEX IN "IDXSPACE5" LONG IN "LONGSPCE1" ;
CREATE TABLE "DB2INST "."FIELD_RESPONSE"  (
                  "FIELD_RESPONSE_ID" INTEGER NOT NULL ,
                  "CUSTOM_FIELD_ID" INTEGER ,
                  "NAME" VARCHAR(100) NOT NULL ,
                  "RESPONSE" VARCHAR(100) ,
                  "RESPONSE_LONG" CLOB(256000) LOGGED NOT COMPACT ,
                  "FIELD_RESPONSE_100" VARCHAR(100) ,
                 COMPRESS YES
                 DATA CAPTURE CHANGES
                 IN "USERSPACE1" INDEX IN "IDXSPACE1" LONG IN "LONGSPCE1" ;

Upvotes: 0

anishsane
anishsane

Reputation: 20980

A slight modification to your code: uses gensub...

awk '/^CREATE TABLE/ {print gensub(/GENERATED ALWAYS .*\n/,",\n","g") "\n";}' FS="\n" RS="" src.ddl > tables.ddl

Upvotes: 0

fedorqui
fedorqui

Reputation: 290075

sed can be a good tool for this:

sed 's/GENERATED ALWAYS AS.*$/,/' file

This gets everything from GENERATED ALWAYS AS up to the end of line and replaces it with a comma.

In case you want to do in-place editing, use -i as this. It will create a file.bak with the current content and file will contain the new version.

sed -i.bak 's/GENERATED ALWAYS AS.*$/,/' file

Upvotes: 2

Related Questions