0

I am trying to search for a particular pattern in a file and move it to a new files one by one. I tried sed -n 's/CREATE PROCEDURE/,/END ;/p' File It is again printing the same file. The given below is the file contents.

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELORDERLINE ( 
    IN ORDERID INTEGER , 
    IN ORDERLINEID INTEGER , 
    IN NEWORDLINESTATUSCODE VARCHAR(10) , 
    IN NAME VARCHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELORDERLINE 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN ATOMIC 

    DECLARE V_ORDLINENUMFORDSP VARCHAR ( 16 ) ; 

    DECLARE V_ORDLINENUMSTR VARCHAR ( 3 ) ; 

    DECLARE V_ORDLINENUM INT ; 

    DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 

    DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 



DECLARE V_ORDLNSTATUSHISTID INT ; 

    DECLARE V_ORDERID INT DEFAULT 0 ; 

    DECLARE V_ORDERLINEID INT DEFAULT 0 ; 

    DECLARE V_NEWORDLINESTATUSCODE VARCHAR ( 10 ) DEFAULT '' ; 

DECLARE V_OLDSTATUS VARCHAR ( 100 ) DEFAULT '' ; 

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 

        SET O_RESULT = SQLSTATE ; 

        SET O_RESULT = V_RESULT ; 


SET V_ORDERID = ORDERID ; 
SET V_ORDERLINEID = ORDERLINEID ; 
SET V_NEWORDLINESTATUSCODE = NEWORDLINESTATUSCODE ; 


/* Insert a new record for the new line OrdLineStatusHist*/ 
    SET V_OLDSTATUS = ( SELECT OLSH . ORDLNSTATUSTYPECD FROM JOES . ORDERLINESTATUSHIST OLSH WHERE OLSH . CLIENTORDERID = V_ORDERID AND OLSH . CLIENTORDERLINEID = V_ORDERLINEID AND OLSH . STATUSENDDATETIME IS NULL ) ; 

    IF ( ( V_OLDSTATUS IS NULL ) OR ( V_OLDSTATUS <> V_NEWORDLINESTATUSCODE ) ) THEN 


        IF ( EXISTS ( SELECT ORDLNSTATUSHISTID FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ) 
        THEN 
            SET V_ORDLNSTATUSHISTID = ( SELECT MAX ( ORDLNSTATUSHISTID ) FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ; 
        ELSE 
            SET V_ORDLNSTATUSHISTID = 0 ; 
        END IF ; 


        IF ( V_ORDLNSTATUSHISTID > 0 ) THEN 

        UPDATE  JOES . ORDERLINESTATUSHIST SET STATUSENDDATETIME = CURRENT TIMESTAMP , LASTUPDATENAME = NAME , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE 
        CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID AND ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID ; 

        END IF ; 

        SET V_ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID + 1 ; 

        INSERT INTO JOES . ORDERLINESTATUSHIST ( CLIENTORDERID , CLIENTORDERLINEID , ORDLNSTATUSHISTID , ORDLNSTATUSTYPECD , STATUSSTARTDATETIME , STATUSENDDATETIME , 
        CREATENAME , CREATETIMESTAMP ) VALUES ( V_ORDERID , V_ORDERLINEID , V_ORDLNSTATUSHISTID , V_NEWORDLINESTATUSCODE , CURRENT TIMESTAMP , CURRENT TIMESTAMP , NAME , CURRENT TIMESTAMP ) ; 

        UPDATE JOES . CLIENTORDERLINE SET 
            ORDLNSTATUSTYPECD = V_NEWORDLINESTATUSCODE , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ; 

    END IF ; 

END  ; 

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELTRANSFERNUMBER ( 
    IN TRANSFERNUMBER INTEGER , 
    OUT O_RETURNCODE CHAR(10) , 
    OUT O_RETURNMESSAGE CHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELTRANSFERNUMBER 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 

DECLARE V_SYNONRTNCD CHAR ( 7 ) DEFAULT '' ; 
DECLARE V_RETURMESSAGE CHAR ( 50 ) DEFAULT 'SUCCESS' ; 
DECLARE V_RETURNCODE CHAR ( 10 ) DEFAULT '' ; 
DECLARE V_TRANSFERNUMBER INTEGER ; 
DECLARE V_CHARTRANSFERBATCHNUMBER CHAR ( 15 ) ; 
DECLARE V_TRANSFERBATCHNUMBER INTEGER ; 
DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 
DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 

SET O_RESULT = SQLSTATE ; 
SET O_RESULT = V_RESULT ; 


SET V_TRANSFERNUMBER = TRANSFERNUMBER ; 

SET V_TRANSFERBATCHNUMBER = ( SELECT MAX ( TRANSFERBATCH ) FROM JOES . TRANSFERREQHEADER WHERE TRANSFERNUMBER = V_TRANSFERNUMBER ) ; 
SET V_CHARTRANSFERBATCHNUMBER = CHAR ( V_TRANSFERBATCHNUMBER ) ; 

CALL MWWPROD . BAERXFR ( V_SYNONRTNCD , V_CHARTRANSFERBATCHNUMBER , V_RETURNCODE , V_RETURMESSAGE ) ; 

DELETE FROM JOES . TRANSFERREQHEADER WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 
DELETE FROM JOES . TRANSFERREQLINE WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 

SET O_RETURNCODE = V_RETURNCODE ; 
SET O_RETURNMESSAGE = V_RETURMESSAGE ; 

END  ;

I am trying for a script that will help me split the above file into seperate files. Each file should begin with

SET PATH ......

and end with

END ;

So the output should be like

Contents in File 1:

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELORDERLINE ( 
    IN ORDERID INTEGER , 
    IN ORDERLINEID INTEGER , 
    IN NEWORDLINESTATUSCODE VARCHAR(10) , 
    IN NAME VARCHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELORDERLINE 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN ATOMIC 

    DECLARE V_ORDLINENUMFORDSP VARCHAR ( 16 ) ; 

    DECLARE V_ORDLINENUMSTR VARCHAR ( 3 ) ; 

    DECLARE V_ORDLINENUM INT ; 

    DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 

    DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 



DECLARE V_ORDLNSTATUSHISTID INT ; 

    DECLARE V_ORDERID INT DEFAULT 0 ; 

    DECLARE V_ORDERLINEID INT DEFAULT 0 ; 

    DECLARE V_NEWORDLINESTATUSCODE VARCHAR ( 10 ) DEFAULT '' ; 

DECLARE V_OLDSTATUS VARCHAR ( 100 ) DEFAULT '' ; 

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 

        SET O_RESULT = SQLSTATE ; 

        SET O_RESULT = V_RESULT ; 


SET V_ORDERID = ORDERID ; 
SET V_ORDERLINEID = ORDERLINEID ; 
SET V_NEWORDLINESTATUSCODE = NEWORDLINESTATUSCODE ; 


/* Insert a new record for the new line OrdLineStatusHist*/ 
    SET V_OLDSTATUS = ( SELECT OLSH . ORDLNSTATUSTYPECD FROM JOES . ORDERLINESTATUSHIST OLSH WHERE OLSH . CLIENTORDERID = V_ORDERID AND OLSH . CLIENTORDERLINEID = V_ORDERLINEID AND OLSH . STATUSENDDATETIME IS NULL ) ; 

    IF ( ( V_OLDSTATUS IS NULL ) OR ( V_OLDSTATUS <> V_NEWORDLINESTATUSCODE ) ) THEN 


        IF ( EXISTS ( SELECT ORDLNSTATUSHISTID FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ) 
        THEN 
            SET V_ORDLNSTATUSHISTID = ( SELECT MAX ( ORDLNSTATUSHISTID ) FROM JOES . ORDERLINESTATUSHIST WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ) ; 
        ELSE 
            SET V_ORDLNSTATUSHISTID = 0 ; 
        END IF ; 


        IF ( V_ORDLNSTATUSHISTID > 0 ) THEN 

        UPDATE  JOES . ORDERLINESTATUSHIST SET STATUSENDDATETIME = CURRENT TIMESTAMP , LASTUPDATENAME = NAME , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE 
        CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID AND ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID ; 

        END IF ; 

        SET V_ORDLNSTATUSHISTID = V_ORDLNSTATUSHISTID + 1 ; 

        INSERT INTO JOES . ORDERLINESTATUSHIST ( CLIENTORDERID , CLIENTORDERLINEID , ORDLNSTATUSHISTID , ORDLNSTATUSTYPECD , STATUSSTARTDATETIME , STATUSENDDATETIME , 
        CREATENAME , CREATETIMESTAMP ) VALUES ( V_ORDERID , V_ORDERLINEID , V_ORDLNSTATUSHISTID , V_NEWORDLINESTATUSCODE , CURRENT TIMESTAMP , CURRENT TIMESTAMP , NAME , CURRENT TIMESTAMP ) ; 

        UPDATE JOES . CLIENTORDERLINE SET 
            ORDLNSTATUSTYPECD = V_NEWORDLINESTATUSCODE , UPDTIMESTAMP = CURRENT TIMESTAMP WHERE CLIENTORDERID = V_ORDERID AND CLIENTORDERLINEID = V_ORDERLINEID ; 

    END IF ; 

END  ;

Contents in File 2:

SET PATH "QSYS","QSYS2","JOES" ; 

CREATE PROCEDURE JOES.CANCELTRANSFERNUMBER ( 
    IN TRANSFERNUMBER INTEGER , 
    OUT O_RETURNCODE CHAR(10) , 
    OUT O_RETURNMESSAGE CHAR(50) , 
    OUT O_RESULT CHAR(5) ) 
    LANGUAGE SQL 
    SPECIFIC JOES.CANCELTRANSFERNUMBER 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 

DECLARE V_SYNONRTNCD CHAR ( 7 ) DEFAULT '' ; 
DECLARE V_RETURMESSAGE CHAR ( 50 ) DEFAULT 'SUCCESS' ; 
DECLARE V_RETURNCODE CHAR ( 10 ) DEFAULT '' ; 
DECLARE V_TRANSFERNUMBER INTEGER ; 
DECLARE V_CHARTRANSFERBATCHNUMBER CHAR ( 15 ) ; 
DECLARE V_TRANSFERBATCHNUMBER INTEGER ; 
DECLARE V_RESULT CHAR ( 5 ) DEFAULT '00000' ; 
DECLARE SQLSTATE CHAR ( 5 ) DEFAULT '00000' ; 

SET O_RESULT = SQLSTATE ; 
SET O_RESULT = V_RESULT ; 


SET V_TRANSFERNUMBER = TRANSFERNUMBER ; 

SET V_TRANSFERBATCHNUMBER = ( SELECT MAX ( TRANSFERBATCH ) FROM JOES . TRANSFERREQHEADER WHERE TRANSFERNUMBER = V_TRANSFERNUMBER ) ; 
SET V_CHARTRANSFERBATCHNUMBER = CHAR ( V_TRANSFERBATCHNUMBER ) ; 

CALL MWWPROD . BAERXFR ( V_SYNONRTNCD , V_CHARTRANSFERBATCHNUMBER , V_RETURNCODE , V_RETURMESSAGE ) ; 

DELETE FROM JOES . TRANSFERREQHEADER WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 
DELETE FROM JOES . TRANSFERREQLINE WHERE TRANSFERBATCH = V_TRANSFERBATCHNUMBER ; 

SET O_RETURNCODE = V_RETURNCODE ; 
SET O_RETURNMESSAGE = V_RETURMESSAGE ; 

END  ;

What can be done to achieve this? I am struck here.

Rui F Ribeiro
  • 55,929
  • 26
  • 146
  • 227
sabarish jackson
  • 588
  • 3
  • 14
  • 30

1 Answers1

3

The solution I'm offering starts each file with 'SET PATH'. It does not check for 'END' at the end of the section (but you didn't explain what should happen if a subsequent 'SET PATH' occurred without a preceding 'END', so I'm assuming this isn't a hard requirement).

csplit -kz source.sql '/^SET PATH /' '{*}'

The csplit command splits the source.sql file with the regular expression that corresponds to the string 'SET PATH' anchored to the beginning of the line. There are various flags controlling the names of the output files but by default the output for your sample is put into files xx00 and xx01.

roaima
  • 107,089
  • 14
  • 139
  • 261
  • Hey @roaima, How to check the 'END'(Value) to split a file from start to END in a file and split into pieces. – sabarish jackson Mar 20 '17 at 12:07
  • @sabarishjackson please post that as a new question. I would recommend that you include an short example of the source file and your required output. It may also be helpful to reference this question in your new question, so that people have some context. – roaima Mar 20 '17 at 14:14
  • I did, Could you please take a look into it and help me on it. http://unix.stackexchange.com/questions/352737/select-the-contents-respective-to-some-specific-content-from-a-file-and-move-it – sabarish jackson Mar 21 '17 at 08:58