//DEFTRADE  JOB  <YourJobInfo>
//*
//* 5655-F31 (C) Copyright IBM Corp. 2001
//* All Rights Reserved.
//* U.S. Government users - RESTRICTED RIGHTS - Use, Duplication, or
//* Disclosure restricted by GSA-ADP schedule contract with IBM Corp.
//* Status = H28W401
//* This program may be used, executed, copied, modified and distributed
//* without royalty for the purpose of developing, using, marketing,
//* or distributing.
//*
//*  GET RID OF TRADE TABLES, DATABASES, SPACES AND STORGROUPS
//*
//DROPTAB EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(<YourDb2SubSys>)
 RUN  PROGRAM(DSNTIAD) PLAN(<YourDSNTIADPlan>) -
      LIB('<YourDSNTIADLoadLib>')
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSIN    DD  *
Drop Database TRADEDB;
commit;
/*
//*
//*  DELETE THEN DEFINE THE LINEAR DATASETS.
//*
//DEFVSAM  EXEC PGM=IDCAMS,COND=(8,LT)
//*
//SYSPRINT DD SYSOUT=*
//*
//SYSIN DD *
   DELETE <YourDb2Hlq>.TRADEDB.ACCTBSPC.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.ACCOUNDX.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.HOLDBSPC.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.HOLDINDX.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.PROFBSPC.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.PROFINDX.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.QUOTBSPC.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.QUOTENDX.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.REGBSPC.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.REGISNDX.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.KEYSSPC.I0001.A001
   DELETE <YourDb2Hlq>.TRADEDB.KEYSNDXB.I0001.A001
  SET MAXCC = 0

    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.ACCTBSPC.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.ACCTBSPC.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.ACCOUNDX.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.ACCOUNDX.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.HOLDBSPC.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.HOLDBSPC.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.HOLDINDX.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.HOLDINDX.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.PROFBSPC.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.PROFBSPC.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.PROFINDX.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.PROFINDX.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.QUOTBSPC.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                             -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.QUOTBSPC.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.QUOTENDX.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                             -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.QUOTENDX.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.REGBSPC.I0001.A001)  -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.REGBSPC.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.REGISNDX.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.REGISNDX.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.KEYSSPC.I0001.A001)  -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.KEYSSPC.I0001.A001))
    DEFINE CLUSTER-
            (NAME(<YourDb2Hlq>.TRADEDB.KEYSNDXB.I0001.A001) -
             LINEAR                                         -
             REUSE                                          -
             VOLUMES(<YourVol>)                             -
             RECORDS(4000 2500)                            -
             SHAREOPTIONS(3 3) )                            -
           DATA                                             -
            (NAME(<YourDb2DataHlq>.TRADEDB.KEYSNDXB.I0001.A001))
/*
//*
//*  CREATE TRADE TABLES, DATABASES, SPACES AND STORGROUPS
//*
//*
//DEFTAB  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(8,LT)
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(<YourDb2SubSys>)
 RUN  PROGRAM(DSNTIAD) PLAN(<YourDSNTIADPlan>) -
      LIB('<YourDSNTIADLoadLib>')
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSIN    DD  *


  create database TRADEDB
    BUFFERPOOL BP0;


  CREATE TABLESPACE QuotBSpc IN TRADEDB
         BUFFERPOOL BP0
         LOCKSIZE ROW
         SEGSIZE 32
         USING VCAT <YourDb2VCat>
         PCTFREE 15 ;

  CREATE TABLESPACE AcctBSpc IN TRADEDB
         BUFFERPOOL BP0
         LOCKSIZE ROW
         SEGSIZE 32
         USING VCAT <YourDb2VCat>
         PCTFREE 15 ;

  CREATE TABLESPACE HoldBSpc IN TRADEDB
         BUFFERPOOL BP0
         LOCKSIZE ROW
         SEGSIZE 32
         USING VCAT <YourDb2VCat>
         PCTFREE 15 ;

  CREATE TABLESPACE ProfBSpc IN TRADEDB
         BUFFERPOOL BP0
         LOCKSIZE ROW
         SEGSIZE 32
         USING VCAT <YourDb2VCat>
         PCTFREE 15 ;

  CREATE TABLESPACE RegBSpc IN TRADEDB
         BUFFERPOOL BP0
         LOCKSIZE ROW
         SEGSIZE 32
         USING VCAT <YourDb2VCat>
         PCTFREE 15 ;

  CREATE TABLESPACE KeysSpc IN TRADEDB
         BUFFERPOOL BP0
         LOCKSIZE ROW
         SEGSIZE 32
         USING VCAT <YourDb2VCat>
         PCTFREE 15 ;

CREATE TABLE <Db2DfltQual>.TradeQuoteBean (
  symbol      VARCHAR(251) NOT NULL,
  details     VARCHAR(251),
  price       FLOAT,
  PRIMARY KEY (symbol)) in TRADEDB.QuotBSpc;
commit ;

CREATE Unique INDEX <Db2DfltQual>.QuoteNDXnTbl
      on <Db2DfltQual>.TradeQuoteBean (
  symbol ASC)
  Using
    VCAT <YourDb2VCat>;
commit ;

CREATE TABLE <Db2DfltQual>.TradeAccountBean (
  userid VARCHAR(251) NOT NULL,
  balance FLOAT,
  transactions INTEGER,
  PRIMARY KEY (userid)) in TRADEDB.AcctBSpc;
commit ;

CREATE Unique INDEX <Db2DfltQual>.AccouNDXeanTbl
      on <Db2DfltQual>.TradeAccountBean (
  userid ASC)
  Using
    VCAT <YourDb2VCat>;
commit ;

CREATE TABLE <Db2DfltQual>.TradeHoldingBean (
  userid VARCHAR(128) NOT NULL,
  symbol VARCHAR(251),
  price FLOAT,
  quantity FLOAT,
  indx INTEGER NOT NULL,
  PRIMARY KEY (userid,indx)) in TRADEDB.HoldBSpc;
commit ;

CREATE Unique INDEX <Db2DfltQual>.HoldiNDXeanTbl
      on <Db2DfltQual>.TradeHoldingBean (
  userid ASC, indx ASC)
  Using
    VCAT <YourDb2VCat>;
commit ;

CREATE TABLE <Db2DfltQual>.TradeProfileBean (
  userid VARCHAR(251) NOT NULL,
  fullname VARCHAR(251),
  address VARCHAR(251),
  email VARCHAR(251),
  creditcard VARCHAR(251),
  PRIMARY KEY (userid)) in TRADEDB.ProfBSpc;
commit ;

CREATE Unique INDEX <Db2DfltQual>.ProfiNDXeanTbl
      on <Db2DfltQual>.TradeProfileBean (
  userid ASC)
  Using
    VCAT <YourDb2VCat>;
commit ;

CREATE TABLE <Db2DfltQual>.TradeRegistryBean (
  userid VARCHAR(251) NOT NULL,
  password VARCHAR(251),
  status INTEGER,
  PRIMARY KEY (userid)) in TRADEDB.RegBSpc;
commit ;

CREATE Unique INDEX <Db2DfltQual>.RegisNDXBeanTbl
      on <Db2DfltQual>.TradeRegistryBean (
  userid ASC)
  Using
    VCAT <YourDb2VCat>;
commit ;

CREATE TABLE <Db2DfltQual>.KeysEntityBean
(name VARCHAR(251) NOT NULL,
   value INTEGER,
   PRIMARY KEY (name)) in TRADEDB.KeysSpc;
commit ;

CREATE Unique INDEX <Db2DfltQual>.KeysNDXBeanTbl
             on <Db2DfltQual>.KeysEntityBean (
  name ASC)
  Using
    VCAT <YourDb2VCat>;
commit ;

insert into <Db2DfltQual>.KeysEntityBean( name, value)
       VALUES ('holdingKey', 0);

GRANT DELETE, INSERT, SELECT, UPDATE
      ON TABLE <Db2DfltQual>.TradeQuoteBean
      TO PUBLIC AT ALL LOCATIONS;
commit ;

GRANT DELETE, INSERT, SELECT, UPDATE
      ON TABLE <Db2DfltQual>.TradeAccountBean
      TO PUBLIC AT ALL LOCATIONS;
commit ;

GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE <Db2DfltQual>.TradeHoldingBean
      TO PUBLIC AT ALL LOCATIONS;
commit ;

GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE <Db2DfltQual>.TradeProfileBean
      TO PUBLIC AT ALL LOCATIONS;
commit ;

GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE <Db2DfltQual>.TradeRegistryBean
      TO PUBLIC AT ALL LOCATIONS;
commit ;

GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE <Db2DfltQual>.KeysEntityBean
      TO PUBLIC AT ALL LOCATIONS;
commit ;
/*
//*