1

All I need to do is, search for CCSID in this file, wherever it finds CCSID, the CHAR in that line should be replaced with NCHAR and VARCHAR in that line should be replaced with NVARCHAR2.

I tried using sed and awk. But I couldn't find a perfect way to solve this issue.

 CREATE TABLE JCR.ICMSTSYSCONTROL (
            LIBRARYSERVERID  INTEGER NOT NULL ,
            LANGUAGECODE  CHAR(3) CCSID 37 NOT NULL ,
            SYSSEGMENTID  SMALLINT NOT NULL ,
            SYSSEGMENTTHRESHLD  INTEGER NOT NULL ,
            ACLBINDINGLEVEL  SMALLINT NOT NULL ,
            LIBRARYACLCODE  INTEGER NOT NULL ,
            PUBACCESSENABLED  SMALLINT NOT NULL ,
            DFLTACLCHOICE  SMALLINT NOT NULL ,
            SMSCHOICE SMALLINT NOT NULL ,
            TRACELEVEL SMALLINT NOT NULL ,
            MAXUSERS INTEGER NOT NULL ,
            MAXUSERACTION  SMALLINT NOT NULL ,
            CURRENTUSERS  INTEGER NOT NULL ,
            MAXLOGONRETRY  SMALLINT NOT NULL ,
            PASSWORDDURATION  SMALLINT NOT NULL ,
            SYSADMINEVENTFLAG  SMALLINT NOT NULL ,
            SYSTEMFLAG SMALLINT NOT NULL ,
            DATABASETYPE  SMALLINT NOT NULL ,
            MAXTXDURATION  INTEGER NOT NULL ,
            MAXRESULTSETSIZE  INTEGER NOT NULL ,
            ALLOWTRUSTEDLOGON  SMALLINT NOT NULL ,
            DOCROUTINGUPDATE  INTEGER NOT NULL ,
            DOCROUTINGFREQ  SMALLINT NOT NULL ,
            PLATFORM SMALLINT NOT NULL ,
            SYSTIMEOUT SMALLINT NOT NULL ,
            TIEUSERID CHAR(175) CCSID 37 DEFAULT NULL ,
            TIEPASSWORD  CHAR(72) FOR BIT DATA DEFAULT NULL ,
            DATABASENAME  VARCHAR(128) CCSID 37 NOT NULL ,
            DBSCHEMANAME  VARCHAR(128) CCSID 37 NOT NULL ,
            TRACEFILENAME  VARCHAR(128) CCSID 37 DEFAULT NULL ,
            ENCRYPTIONKEY  VARCHAR(128) FOR BIT DATA NOT NULL ,
            KEEPTRACEOPEN  SMALLINT NOT NULL ,
            MULTIPLETRACEFILES  SMALLINT NOT NULL ,
            MAXTRACEFILESIZE  INTEGER NOT NULL ,
            PATHICMROOT  VARCHAR(128) CCSID 37 NOT NULL ,
            PATHICMDLL VARCHAR(128) CCSID 37 NOT NULL ,
            SUSPENDSERVERTIME  TIMESTAMP DEFAULT NULL ,
            RMSTATUSINTERVAL  SMALLINT NOT NULL ,
            RMSTATUSTIMEOUT  SMALLINT NOT NULL ,
            TIEINTERVAL  SMALLINT NOT NULL ,
            LSCURRENTVERSION  VARCHAR(128) CCSID 37 NOT NULL ,
            TRACEUSER CHAR(175) CCSID 37 DEFAULT NULL ,
            DIMSGDIGESTALGO  SMALLINT NOT NULL DEFAULT 0 ,
            DIENCRYPTIONALGO  SMALLINT NOT NULL DEFAULT 0 ,
            CONSTRAINT JCR.ICMSTSYSCONTROLPK PRIMARY KEY( LIBRARYSERVERID ) )
            ;

The output should be like:

CREATE TABLE JCR.ICMSTSYSCONTROL (
                LIBRARYSERVERID  INTEGER NOT NULL ,
                LANGUAGECODE  NCHAR(3) CCSID 37 NOT NULL ,
                SYSSEGMENTID  SMALLINT NOT NULL ,
                SYSSEGMENTTHRESHLD  INTEGER NOT NULL ,
                ACLBINDINGLEVEL  SMALLINT NOT NULL ,
                LIBRARYACLCODE  INTEGER NOT NULL ,
                PUBACCESSENABLED  SMALLINT NOT NULL ,
                DFLTACLCHOICE  SMALLINT NOT NULL ,
                SMSCHOICE SMALLINT NOT NULL ,
                TRACELEVEL SMALLINT NOT NULL ,
                MAXUSERS INTEGER NOT NULL ,
                MAXUSERACTION  SMALLINT NOT NULL ,
                CURRENTUSERS  INTEGER NOT NULL ,
                MAXLOGONRETRY  SMALLINT NOT NULL ,
                PASSWORDDURATION  SMALLINT NOT NULL ,
                SYSADMINEVENTFLAG  SMALLINT NOT NULL ,
                SYSTEMFLAG SMALLINT NOT NULL ,
                DATABASETYPE  SMALLINT NOT NULL ,
                MAXTXDURATION  INTEGER NOT NULL ,
                MAXRESULTSETSIZE  INTEGER NOT NULL ,
                ALLOWTRUSTEDLOGON  SMALLINT NOT NULL ,
                DOCROUTINGUPDATE  INTEGER NOT NULL ,
                DOCROUTINGFREQ  SMALLINT NOT NULL ,
                PLATFORM SMALLINT NOT NULL ,
                SYSTIMEOUT SMALLINT NOT NULL ,
                TIEUSERID NCHAR(175) CCSID 37 DEFAULT NULL ,
                TIEPASSWORD  CHAR(72) FOR BIT DATA DEFAULT NULL ,
                DATABASENAME  NVARCHAR2(128) CCSID 37 NOT NULL ,
                DBSCHEMANAME  NVARCHAR2(128) CCSID 37 NOT NULL ,
                TRACEFILENAME  NVARCHAR2(128) CCSID 37 DEFAULT NULL ,
                ENCRYPTIONKEY  VARCHAR(128) FOR BIT DATA NOT NULL ,
                KEEPTRACEOPEN  SMALLINT NOT NULL ,
                MULTIPLETRACEFILES  SMALLINT NOT NULL ,
                MAXTRACEFILESIZE  INTEGER NOT NULL ,
                PATHICMROOT  NVARCHAR2(128) CCSID 37 NOT NULL ,
                PATHICMDLL NVARCHAR2(128) CCSID 37 NOT NULL ,
                SUSPENDSERVERTIME  TIMESTAMP DEFAULT NULL ,
                RMSTATUSINTERVAL  SMALLINT NOT NULL ,
                RMSTATUSTIMEOUT  SMALLINT NOT NULL ,
                TIEINTERVAL  SMALLINT NOT NULL ,
                LSCURRENTVERSION  NVARCHAR2(128) CCSID 37 NOT NULL ,
                TRACEUSER NCHAR(175) CCSID 37 DEFAULT NULL ,
                DIMSGDIGESTALGO  SMALLINT NOT NULL DEFAULT 0 ,
                DIENCRYPTIONALGO  SMALLINT NOT NULL DEFAULT 0 ,
                CONSTRAINT JCR.ICMSTSYSCONTROLPK PRIMARY KEY( LIBRARYSERVERID ) )
                ;

How to solve this?

3

With sed:

sed '/CCSID/ { s/ CHAR(/ NCHAR(/; s/ VARCHAR(/ NVARCHAR2(/ }' file
  • The first pattern searches for lines with containing CCSID. Then the part inside {...} takes effect.
    • s/ CHAR(/ NCHAR(/; replaces CHAR( (with a leading space) with NCHAR(.
    • s/ VARCHAR(/ NVARCHAR2(/ and replace VARCHAR( with NVARCHAR2(.
  • 2
    leading space is not a good idea since you can use \b or \< : s/\bCHAR(/N&/;s/\bVARCHAR\b/N&2/ – Costas Sep 29 '15 at 11:04
  • Thats working fine. – sabarish jackson Sep 29 '15 at 11:12
0
perl -pe '/CCSID/ and s/\bCHAR\b/N$&/ || s/\bVARCHAR\b/N$&2(/' file 

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.