-- This CLP file was created using DB2LOOK Version "9.7" 
-- Timestamp: 9.9.2011 18:44:46
-- Database Name: ECOLI          
-- Database Manager Version: DB2/NT Version 9.7.4          
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY


CONNECT TO ECOLI;



------------------------------------------------
-- DDL Statements for table "ACA     "."GEN"
------------------------------------------------
 

CREATE TABLE "ACA     "."GEN"  (
		  "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +9223372036854775807  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "NAZIV" VARCHAR(20) NOT NULL , 
		  "KOMPLAMENT" CHAR(1) NOT NULL , 
		  "POCETAK" INTEGER NOT NULL , 
		  "KRAJ" INTEGER NOT NULL , 
		  "KOD" LONG VARCHAR NOT NULL , 
		  "ORGANIZAM_ID" BIGINT )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "ACA     "."GEN"

ALTER TABLE "ACA     "."GEN" 
	ADD CONSTRAINT "CC1309703857031" PRIMARY KEY
		("ID");



------------------------------------------------
-- DDL Statements for table "ACA     "."ORGANIZAM"
------------------------------------------------
 

CREATE TABLE "ACA     "."ORGANIZAM"  (
		  "ID" BIGINT NOT NULL , 
		  "NAZIV" VARCHAR(50) )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "ACA     "."ORGANIZAM"

ALTER TABLE "ACA     "."ORGANIZAM" 
	ADD CONSTRAINT "CC1315488071301" PRIMARY KEY
		("ID");



------------------------------------------------
-- DDL Statements for table "ACA     "."KODON"
------------------------------------------------
 

CREATE TABLE "ACA     "."KODON"  (
		  "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +9223372036854775807  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "KOMPLAMENT" CHAR(1) NOT NULL , 
		  "POCETAK" INTEGER NOT NULL , 
		  "KODON" VARCHAR(3) NOT NULL , 
		  "POCETAK_GENA" INTEGER NOT NULL , 
		  "KRAJ_GENA" INTEGER NOT NULL , 
		  "POZICIJA_P8SK" INTEGER , 
		  "ID_ORGANIZMA" BIGINT )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "ACA     "."KODON"

ALTER TABLE "ACA     "."KODON" 
	ADD CONSTRAINT "CC1309709091193" PRIMARY KEY
		("ID");



ALTER TABLE "ACA     "."KODON" ALTER COLUMN "ID" RESTART WITH 7359495;

------------------------------------------------
-- DDL Statements for table "ACA     "."KODONAMINO"
------------------------------------------------
 

CREATE TABLE "ACA     "."KODONAMINO"  (
		  "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +9223372036854775807  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "KODON" VARCHAR(3) NOT NULL , 
		  "AMINO" CHAR(1) NOT NULL )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "ACA     "."KODONAMINO"

ALTER TABLE "ACA     "."KODONAMINO" 
	ADD CONSTRAINT "CC1309780813197" PRIMARY KEY
		("ID");



ALTER TABLE "ACA     "."KODONAMINO" ALTER COLUMN "ID" RESTART WITH 65;

------------------------------------------------
-- DDL Statements for table "ACA     "."AMINOUKUPNO"
------------------------------------------------
 

CREATE TABLE "ACA     "."AMINOUKUPNO"  (
		  "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +9223372036854775807  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "AMINO" CHAR(10) , 
		  "UKKODZAAMINO" INTEGER , 
		  "UKKODONA" INTEGER , 
		  "ID_ORGANIZMA" BIGINT )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "ACA     "."AMINOUKUPNO"

ALTER TABLE "ACA     "."AMINOUKUPNO" 
	ADD CONSTRAINT "CC1315560326705" PRIMARY KEY
		("ID");



ALTER TABLE "ACA     "."AMINOUKUPNO" ALTER COLUMN "ID" RESTART WITH 101;

------------------------------------------------
-- DDL Statements for table "ACA     "."ORGANIZAMUKUPNO"
------------------------------------------------
 

CREATE TABLE "ACA     "."ORGANIZAMUKUPNO"  (
		  "ID_ORGANIZMA" BIGINT NOT NULL , 
		  "UKKODONA" INTEGER NOT NULL )   
		 IN "USERSPACE1" ; 






------------------------------------------------
-- DDL Statements for table "ACA     "."KODONAMINOPOZICIJAP8SK"
------------------------------------------------
 

CREATE TABLE "ACA     "."KODONAMINOPOZICIJAP8SK"  (
		  "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +9223372036854775807  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "KODON" VARCHAR(3) , 
		  "POZICIJA_P8SK" INTEGER , 
		  "AMINO" CHAR(1) , 
		  "OBA" INTEGER , 
		  "UK_OBA_PROC" REAL , 
		  "AMINO_OBA_PROC" REAL , 
		  "UK_OBA_GEOM" REAL , 
		  "AMINO_OBA_GEOM" REAL , 
		  "UK_OBA_GEOM_ODST" REAL , 
		  "AMINO_OBA_GEOM_ODST" REAL , 
		  "UK_OBA_ARIT" REAL , 
		  "AMINO_OBA_ARIT" REAL , 
		  "UK_OBA_ARIT_ODST" REAL , 
		  "AMINO_OBA_ARIT_ODST" REAL , 
		  "ID_ORGANIZMA" BIGINT )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "ACA     "."KODONAMINOPOZICIJAP8SK"

ALTER TABLE "ACA     "."KODONAMINOPOZICIJAP8SK" 
	ADD CONSTRAINT "CC1310025772074" PRIMARY KEY
		("ID");



-- DDL Statements for foreign keys on Table "ACA     "."GEN"

ALTER TABLE "ACA     "."GEN" 
	ADD CONSTRAINT "CC1315489147032" FOREIGN KEY
		("ORGANIZAM_ID")
	REFERENCES "ACA     "."GEN"
		("ID")
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;






---------------------------------
-- DDL statements for User Defined Functions
---------------------------------

SET CURRENT SCHEMA = "ACA     ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ACA";

CREATE FUNCTION EXPLAIN_GET_MSGS( EXPLAIN_REQUESTER VARCHAR(128),      
           EXPLAIN_TIME      TIMESTAMP,                               
                    SOURCE_NAME       VARCHAR(128),                   
                             SOURCE_SCHEMA     VARCHAR(128),          
                                      SOURCE_VERSION    VARCHAR(64),  
                                               EXPLAIN_LEVEL     CHAR(1),
                                                     STMTNO           
INTEGER,                                                      SECTNO  
         INTEGER,                                                     
INLOCALE          VARCHAR(33) )                                       
         RETURNS TABLE ( EXPLAIN_REQUESTER VARCHAR(128),              
                  EXPLAIN_TIME      TIMESTAMP,                        
                           SOURCE_NAME       VARCHAR(128),            
                                    SOURCE_SCHEMA     VARCHAR(128),   
                                             SOURCE_VERSION    VARCHAR(64),
                                                 EXPLAIN_LEVEL     CHAR(1),
                                                     STMTNO           
INTEGER,                                                      SECTNO  
         INTEGER,                                                     
DIAGNOSTIC_ID     INTEGER,                                            
         LOCALE            VARCHAR(33),                               
                  MSG               VARCHAR(4096) )                   
                           SPECIFIC EXPLAIN_GET_MSGS                  
                                    LANGUAGE SQL                      
                                             DETERMINISTIC            
                                                      NO EXTERNAL ACTION
                                                             READS SQL
DATA                                                                  RETURN
SELECT A.A_EXPLAIN_REQUESTER,                                         
  A.A_EXPLAIN_TIME,                                                   
           A.A_SOURCE_NAME,                                           
                    A.A_SOURCE_SCHEMA,                                
                             A.A_SOURCE_VERSION,                      
                                      A.A_EXPLAIN_LEVEL,              
                                               A.A_STMTNO,            
                                                        A.A_SECTNO,   
                                                                 A.A_DIAGNOSTIC_ID,
                                                             F.LOCALE,
                                                                      F.MSG
                                                                      
      FROM EXPLAIN_DIAGNOSTIC A( A_EXPLAIN_REQUESTER,                 
                                          A_EXPLAIN_TIME,             
                                                   A_SOURCE_NAME,     
                                                            A_SOURCE_SCHEMA,
                                                               A_SOURCE_VERSION,
                                                              A_EXPLAIN_LEVEL,
                                                               A_STMTNO,
                                                                      A_SECTNO,
                                                                      A_DIAGNOSTIC_ID,
                                                               A_CODE ),
                                                               TABLE( SYSPROC.EXPLAIN_GET_MSG2(
                                                      INLOCALE,       
                                                               A.A_CODE,
                                                                      (
SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B                           
                     WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                                            AND A.A_EXPLAIN_TIME      =
B.EXPLAIN_TIME                                                    AND A.A_SOURCE_NAME
      = B.SOURCE_NAME                                                 
   AND A.A_SOURCE_SCHEMA     = B.SOURCE_SCHEMA                        
                          AND A.A_SOURCE_VERSION    = B.SOURCE_VERSION
                                                 AND A.A_EXPLAIN_LEVEL
    = B.EXPLAIN_LEVEL                                                 
 AND A.A_STMTNO            = B.STMTNO                                 
                        AND A.A_SECTNO            = B.SECTNO          
                                               AND A.A_DIAGNOSTIC_ID  
  = B.DIAGNOSTIC_ID                                                   AND
B.ORDINAL=1 ),                                                        
                   ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B      
                                          WHERE A.A_EXPLAIN_REQUESTER =
B.EXPLAIN_REQUESTER                                             AND A.A_EXPLAIN_TIME
     = B.EXPLAIN_TIME                                                 
  AND A.A_SOURCE_NAME       = B.SOURCE_NAME                           
                         AND A.A_SOURCE_SCHEMA     = B.SOURCE_SCHEMA  
                                                AND A.A_SOURCE_VERSION
   = B.SOURCE_VERSION                                                 
AND A.A_EXPLAIN_LEVEL     = B.EXPLAIN_LEVEL                           
                       AND A.A_STMTNO            = B.STMTNO           
                                              AND A.A_SECTNO          
 = B.SECTNO                                                          AND
A.A_DIAGNOSTIC_ID     = B.DIAGNOSTIC_ID                               
                   AND B.ORDINAL=2 ),                                 
                                          ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA
B                                                 WHERE A.A_EXPLAIN_REQUESTER
= B.EXPLAIN_REQUESTER                                             AND A.A_EXPLAIN_TIME
     = B.EXPLAIN_TIME                                                 
  AND A.A_SOURCE_NAME       = B.SOURCE_NAME                           
                         AND A.A_SOURCE_SCHEMA     = B.SOURCE_SCHEMA  
                                                AND A.A_SOURCE_VERSION
   = B.SOURCE_VERSION                                                 
AND A.A_EXPLAIN_LEVEL     = B.EXPLAIN_LEVEL                           
                       AND A.A_STMTNO            = B.STMTNO           
                                              AND A.A_SECTNO          
 = B.SECTNO                                                          AND
A.A_DIAGNOSTIC_ID     = B.DIAGNOSTIC_ID                               
                   AND B.ORDINAL=3 ) ) ) F                            
                                  WHERE ( EXPLAIN_REQUESTER IS NULL OR
                                                                 EXPLAIN_REQUESTER
= A.A_EXPLAIN_REQUESTER )                                             
     AND ( EXPLAIN_TIME      IS NULL OR                               
                            EXPLAIN_TIME      = A.A_EXPLAIN_TIME      )
                                                  AND ( SOURCE_NAME   
   IS NULL OR                                                         
  SOURCE_NAME       = A.A_SOURCE_NAME       )                         
                         AND ( SOURCE_SCHEMA     IS NULL OR           
                                                SOURCE_SCHEMA     = A.A_SOURCE_SCHEMA
    )                                                   AND ( SOURCE_VERSION
   IS NULL OR                                                         
  SOURCE_VERSION    = A.A_SOURCE_VERSION    )                         
                         AND ( EXPLAIN_LEVEL     IS NULL OR           
                                                EXPLAIN_LEVEL     = A.A_EXPLAIN_LEVEL
    )                                                   AND ( STMTNO  
         IS NULL OR                                                   
        STMTNO            = A.A_STMTNO            )                   
                               AND ( SECTNO            IS NULL OR     
                                                      SECTNO          
 = A.A_SECTNO            );


----------------------------

-- DDL Statements for Views

----------------------------
SET CURRENT SCHEMA = "ACA     ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ACA";
create view odstupanjap8sk as select id_organizma, amino, kodon, sum(case
pozicija_p8sk when 0 then amino_oba_geom_odst else 0 end) godst0, sum(case
pozicija_p8sk when 0 then amino_oba_arit_odst else 0 end) aodst0, sum(case
pozicija_p8sk when 1 then amino_oba_geom_odst else 0 end) godst1, sum(case
pozicija_p8sk when 1 then amino_oba_arit_odst else 0 end) aodst1, sum(case
pozicija_p8sk when 2 then amino_oba_geom_odst else 0 end) godst2, sum(case
pozicija_p8sk when 2 then amino_oba_arit_odst else 0 end) aodst2, sum(case
pozicija_p8sk when 3 then amino_oba_geom_odst else 0 end) godst3, sum(case
pozicija_p8sk when 3 then amino_oba_arit_odst else 0 end) aodst3, sum(case
pozicija_p8sk when 4 then amino_oba_geom_odst else 0 end) godst4, sum(case
pozicija_p8sk when 4 then amino_oba_arit_odst else 0 end) aodst4, sum(case
pozicija_p8sk when 5 then amino_oba_geom_odst else 0 end) godst5, sum(case
pozicija_p8sk when 5 then amino_oba_arit_odst else 0 end) aodst5, sum(case
pozicija_p8sk when 6 then amino_oba_geom_odst else 0 end) godst6, sum(case
pozicija_p8sk when 6 then amino_oba_arit_odst else 0 end) aodst6, sum(case
pozicija_p8sk when 7 then amino_oba_geom_odst else 0 end) godst7, sum(case
pozicija_p8sk when 7 then amino_oba_arit_odst else 0 end) aodst7, sum(case
pozicija_p8sk when 8 then amino_oba_geom_odst else 0 end) godst8, sum(case
pozicija_p8sk when 8 then amino_oba_arit_odst else 0 end) aodst8, sum(case
pozicija_p8sk when 9 then amino_oba_geom_odst else 0 end) godst9, sum(case
pozicija_p8sk when 9 then amino_oba_arit_odst else 0 end) aodst9 from kodonaminopozicijaP8SK
group by id_organizma, amino, kodon;




COMMIT WORK;

CONNECT RESET;

TERMINATE;

-- Specified SCHEMA is: ACA
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
;