Quelle  yts766.mco

-- SQL Test Suite, V6.0, SQL Module yts766.mco
-- 59-byte ID
-- 
MODULE YTS766
LANGUAGE COBOL
AUTHORIZATION CTS1



PROCEDURE SUB1 (SQLCODE, SQLSTATE,
    :UIDX CHAR(18));
      SELECT USER INTO :UIDX FROM CTS1.ECCO;

PROCEDURE SUB2 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB3 (SQLCODE, SQLSTATE,
    :CONNAM CHAR(128));
      SELECT CONSTRAINT_NAME INTO :CONNAM
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE TABLE_NAME = 'WORKS3A' AND 
                    CONSTRAINT_TYPE = 'FOREIGN KEY';

PROCEDURE SUB4 (SQLCODE, SQLSTATE,
    :UNICON CHAR(128),
    :MOPT CHAR(128),
    :UPR CHAR(128),
    :DELR CHAR(128),
    :CONNAM CHAR(128));
      SELECT UNIQUE_CONSTRAINT_SCHEMA, MATCH_OPTION, 
                    UPDATE_RULE,DELETE_RULE 
                    INTO :UNICON, :MOPT, :UPR, :DELR
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE CONSTRAINT_NAME = :CONNAM;

PROCEDURE SUB5 (SQLCODE, SQLSTATE,
    :CONNAM CHAR(128));
      SELECT CONSTRAINT_NAME INTO :CONNAM
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE TABLE_NAME = 'COMP_BUDG' AND
                    CONSTRAINT_TYPE = 'FOREIGN KEY';

PROCEDURE SUB6 (SQLCODE, SQLSTATE,
    :UNICON CHAR(128),
    :MOPT CHAR(128),
    :UPR CHAR(128),
    :DELR CHAR(128),
    :CONNAM CHAR(128));
      SELECT UNIQUE_CONSTRAINT_SCHEMA, MATCH_OPTION, 
                    UPDATE_RULE,DELETE_RULE 
                    INTO :UNICON, :MOPT, :UPR, :DELR
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE CONSTRAINT_NAME = :CONNAM;

PROCEDURE SUB7 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB8 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7540','pass','MCO');

PROCEDURE SUB9 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7540','fail','MCO');

PROCEDURE SUB10 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB11 (SQLCODE, SQLSTATE,
    :CONNAM CHAR(128));
      SELECT CONSTRAINT_NAME INTO :CONNAM
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE TABLE_NAME = 'STAFFZ' AND
                    CONSTRAINT_TYPE = 'FOREIGN KEY';

PROCEDURE SUB12 (SQLCODE, SQLSTATE,
    :UNICON CHAR(128),
    :MOPT CHAR(128),
    :UPR CHAR(128),
    :DELR CHAR(128),
    :CONNAM CHAR(128));
      SELECT UNIQUE_CONSTRAINT_SCHEMA, MATCH_OPTION,
                    UPDATE_RULE, DELETE_RULE INTO :UNICON, :MOPT, :UPR, :DELR
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
                    WHERE CONSTRAINT_NAME = :CONNAM;

PROCEDURE SUB13 (SQLCODE, SQLSTATE,
    :CONNAM CHAR(128));
      SELECT CONSTRAINT_NAME INTO :CONNAM
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE TABLE_NAME = 'PROJ_STATUS' AND
                    CONSTRAINT_TYPE = 'FOREIGN KEY';

PROCEDURE SUB14 (SQLCODE, SQLSTATE,
    :UNICON CHAR(128),
    :MOPT CHAR(128),
    :UPR CHAR(128),
    :DELR CHAR(128),
    :CONNAM CHAR(128));
      SELECT UNIQUE_CONSTRAINT_SCHEMA, MATCH_OPTION,
                    UPDATE_RULE, DELETE_RULE INTO :UNICON, :MOPT, :UPR, :DELR
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
                    WHERE CONSTRAINT_NAME = :CONNAM;

PROCEDURE SUB15 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB16 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7541','pass','MCO');

PROCEDURE SUB17 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7541','fail','MCO');

PROCEDURE SUB18 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB19 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE CONSTRAINT_CATALOG IS NULL;

PROCEDURE SUB20 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE CONSTRAINT_SCHEMA IS NULL;

PROCEDURE SUB21 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE CONSTRAINT_NAME IS NULL;

PROCEDURE SUB22 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

PROCEDURE SUB23 (SQLCODE, SQLSTATE,
    :CO2 NUMERIC (4));
      SELECT COUNT (*) INTO :CO2
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS T
                    WHERE
                    UNIQUE (
                    SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE CONSTRAINT_CATALOG = T.CONSTRAINT_CATALOG AND
                    CONSTRAINT_NAME = T.CONSTRAINT_NAME AND
                    CONSTRAINT_SCHEMA = T.CONSTRAINT_SCHEMA
                    );

PROCEDURE SUB24 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB25 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7542','pass','MCO');

PROCEDURE SUB26 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7542','fail','MCO');

PROCEDURE SUB27 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB28 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE UNIQUE_CONSTRAINT_CATALOG IS NULL;

PROCEDURE SUB29 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE UNIQUE_CONSTRAINT_SCHEMA IS NULL;

PROCEDURE SUB30 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT(*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE UNIQUE_CONSTRAINT_NAME IS NULL;

PROCEDURE SUB31 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE MATCH_OPTION IS NULL;

PROCEDURE SUB32 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE UPDATE_RULE IS NULL;

PROCEDURE SUB33 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE DELETE_RULE IS NULL;

PROCEDURE SUB34 (SQLCODE, SQLSTATE,
    :CO NUMERIC (4));
      SELECT COUNT (*) INTO :CO
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

PROCEDURE SUB35 (SQLCODE, SQLSTATE,
    :CO2 NUMERIC (4));
      SELECT COUNT (*) INTO :CO2
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE MATCH_OPTION IN ('NONE','PARTIAL','FULL');

PROCEDURE SUB36 (SQLCODE, SQLSTATE,
    :CO2 NUMERIC (4));
      SELECT COUNT (*) INTO :CO2
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE UPDATE_RULE IN
                    ('CASCADE','SET NULL','SET DEFAULT','NO ACTION');

PROCEDURE SUB37 (SQLCODE, SQLSTATE,
    :CO2 NUMERIC (4));
      SELECT COUNT (*) INTO :CO2
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
                    WHERE DELETE_RULE IN
                    ('CASCADE','SET NULL','SET DEFAULT','NO ACTION');

PROCEDURE SUB38 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB39 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7543','pass','MCO');

PROCEDURE SUB40 (SQLCODE, SQLSTATE);
      INSERT INTO CTS1.TESTREPORT
                      VALUES('7543','fail','MCO');

PROCEDURE SUB41 (SQLCODE, SQLSTATE);
      COMMIT WORK;