﻿* SET_DB_I.TXT HARASAWA
I-COMMENT_BEGIN
=WRITE1_OPEN{?_SYSTEM?_INSTALL.TXT}{}{};
=SET{W='CREATE DATABASE I_?_SYSTEM? ON ?WI_DRIVE? USING CODESET IBM-943 TERRITORY JP COLLATE  USING SYSTEM USER'
=+' TABLESPACE MANAGED BY SYSTEM USING(''?W_D1??_P??W_IS?USER'')'
=+'EXTENTSIZE 16 PREFETCHSIZE 16 OVERHEAD 14.06 TRANSFERRATE 0.33 CATALOG TABLESPACE MANAGED BY SYSTEM  USING'
=+'(''?W_D1??_P??W_IS?CATALOG'')'
=+'EXTENTSIZE 16 PREFETCHSIZE 16 OVERHEAD 14.06 TRANSFERRATE 0.33 TEMPORARY TABLESPACE MANAGED BY SYSTEM USING'
=+'(''?W_D1??_P??W_IS?TEMPORARY'')'
=+' EXTENTSIZE 16 PREFETCHSIZE 16 OVERHEAD 14.06 TRANSFERRATE 0.33'};
=WRITE1{W};WRITE1_CLOSE{};
=WRITE1_OPEN{?_SYSTEM?_INSTALL.BAT}{}{};
=SET{WREAL_TEST='REAL'};
=SET{W='DB2CMD -I -C -W DB2 -F ?_SYSTEM?_INSTALL.TXT'};WRITE1{W};
**************( NET USER )***************************************
=SET{W_PER='ZZZZ'};
NAME=LOGIN
REPEAT=2 (W_MW##)
REPEAT2=4 (W_DIOL$$)
=MESSAGE{NET USER (?W_RT##?,?W_PER?)}; 
$1+=SET{WPAS=PASSWORD_?W_RT##?1};
$4+=SET{WPAS=PASSWORD_LINK1};
=SET{W='NET USER                ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER? ?WPAS? /ADD /EXPIRES:NEVER /PASSWORDCHG:NO'};WRITE1{W};
=SET{W='NET LOCALGROUP DB2USERS ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER? /ADD'};WRITE1{W};
=SET{W='NET LOCALGROUP USERS    ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER? /DELETE'};WRITE1{W};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=LOGIN
=SET{W='@ECHO OFF'};WRITE1{W};
=SET{W='ECHO *****************************************************************'};WRITE1{W};
=SET{W='ECHO *** 注意：全ユーザを「パスワードを無制限にする」に変更下さい。***'};WRITE1{W};
=SET{W='ECHO *** お願い：エラー状況確認後[Enetr]を押して続行して下さい。   ***'};WRITE1{W};
=SET{W='ECHO *****************************************************************'};WRITE1{W};
=SET{W='PAUSE'};WRITE1{W};
=WRITE1_CLOSE{};
=COMMAND{?_SYSTEM?_INSTALL.BAT};
=MESSAGE{GRANT ...};
=LOGIN_SQL{GRANT DBADM,CREATE_NOT_FENCED,IMPLICIT_SCHEMA ON DATABASE
= TO USER ?W_IS?REAL_DBO_ZZZZ}ERROR{?_DB_ERROR? ?_ERROR?};
=LOGIN_SQL{GRANT SELECT ON SYSIBM.SYSDUMMY1 TO PUBLIC}
=  IF{_DB_ERROR!='I-601'}ERROR{?_DB_ERROR??_ERROR?};
*************************( CREATE SCHEMA )******************************
=MESSAGE{CREATE SCHEMA};
=SET{W_PER='ZZZZ'};
NAME=SCHEMA
REPEAT=2  (W_RT##=REAL,TEST)
REPEAT2=2 (W_MW$$=MAST,WORK) 
=LOGIN_SQL{CREATE SCHEMA ?W_IS??W_RT##?_?W_MW$$?_?W_PER? AUTHORIZATION ?W_IS??W_RT##?_DBO_?W_PER?}
=  IF{_DB_ERROR!='I-601'}ERROR{?_DB_ERROR? ?_ERROR?};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=SCHEMA
***************************************************************************************
=SET{W_PER='ZZZZ'};
NAME=ADDROLLMEMBER
=MESSAGE{EXEC EXEC SP_ADDROLEMEMBER (?W_PER?)};
REPEAT=3 
REPEAT2=2 (W_PER$$(ZZZZ,自分))
=LOGIN_SQL{GRANT ALTERIN,CREATEIN,DROPIN ON SCHEMA ?W_IS??W_RT_A##?_MAST_?W_PER? TO ?W_IS?REAL_DBO_ZZZZ};
=LOGIN_SQL{GRANT ALTERIN,CREATEIN,DROPIN ON SCHEMA ?W_IS??W_RT_A##?_MAST_?W_PER? TO ?W_IS??W_RT_B##?_DBO_?W_PER$$?};
=LOGIN_SQL{GRANT ALTERIN,CREATEIN,DROPIN ON SCHEMA ?W_IS??W_RT_A##?_WORK_?W_PER? TO ?W_IS??W_RT_B##?_DBO_?W_PER$$?};
=LOGIN_SQL{GRANT ALTERIN,CREATEIN,DROPIN ON SCHEMA ?W_IS??W_RT_A##?_WORK_?W_PER? TO ?W_IS??W_RT_B##?_INP_?W_PER$$?};
=LOGIN_SQL{GRANT ALTERIN,CREATEIN,DROPIN ON SCHEMA ?W_IS??W_RT_A##?_WORK_?W_PER? TO ?W_IS??W_RT_B##?_OUT_?W_PER$$?};
=IF{W_PER='ZZZZ'}JUMP{J};
REPEAT2=
=J:
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=ADDROLLMEMBER
*************************( VIEW )******************************
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL'};
=SET{W_PER='ZZZZ'};
NAME=VIEW
=SET{W_M=_M_?W_REAL_TEST?};
REPEAT=2
=MESSAGE{CREATE VIEW(?W_REAL_TEST?,?W_PER?)};
=SET{W_SCHEMA='?W_IS??W_REAL_TEST?_?W_MW##?_?W_PER?'};
***( VIEW ZZZZ_SYS_OBJECT_VIEW )***
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_TEXT,ZZZZ_ADD_DATE
=,ZZZZ_CHANGE_DATE) AS
= SELECT CAST('TABLE' AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(NAME AS CHAR(30)),CAST(NULL AS VARCHAR(4000)),CTIME
=,CAST(NULL AS TIMESTAMP) FROM SYSIBM.SYSTABLES WHERE 
= CREATOR='?W_SCHEMA?' AND TYPE='T'
=UNION ALL SELECT CAST('VIEW' AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(V.NAME AS CHAR(30)),CAST(V.TEXT AS VARCHAR(4000)),T.CTIME
=,CAST(NULL AS TIMESTAMP) FROM SYSIBM.SYSVIEWS V
=,SYSIBM.SYSTABLES T WHERE
=     V.CREATOR='?W_SCHEMA?' AND V.NAME=T.NAME
=AND  T.CREATOR='?W_SCHEMA?'
=UNION ALL SELECT CAST('FUNC' AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(NAME AS CHAR(30)),CAST(BODY AS VARCHAR(4000)),CREATE_TIME
=,CAST(NULL AS TIMESTAMP) FROM SYSIBM.SYSFUNCTIONS WHERE
= SCHEMA='?W_SCHEMA?'
=UNION ALL SELECT CAST('PROC' AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(PROCNAME AS CHAR(30)),CAST(TEXT AS VARCHAR(4000)),CREATE_TIME
=,CAST(NULL AS TIMESTAMP) FROM  SYSIBM.SYSPROCEDURES WHERE
= PROCSCHEMA='?W_SCHEMA?' 
=UNION ALL SELECT CAST('TRIG' AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(NAME AS CHAR(30)),CAST(TEXT AS VARCHAR(4000)),CREATE_TIME
=,CAST(NULL AS TIMESTAMP) FROM SYSIBM.SYSTRIGGERS WHERE
= SCHEMA='?W_SCHEMA?' 
=}  IF{_DB_ERROR!='I-601'}ERROR{?_DB_ERROR??_ERROR?};
****( VIEW ZZZZ_SYS_GRANT_VIEW )*** 
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_GRANT,ZZZZ_SELECT
=,ZZZZ_INSERT,ZZZZ_UPDATE,ZZZZ_DELETE,ZZZZ_EXECUTE)AS
=SELECT CAST(CASE T.TYPE WHEN 'V' THEN 'VIEW'
= ELSE 'TABLE' END AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3)),CAST(T.NAME AS CHAR(30))
=,CAST(CASE WHEN LENGTH(RTRIM(G.GRANTEE))>8 THEN SUBSTR(G.GRANTEE,LENGTH(RTRIM(G.GRANTEE))-7)
= ELSE G.GRANTEE END AS CHAR(8))
=,CAST(CASE G.SELECTAUTH WHEN 'N' THEN ' ' ELSE 'S' END
= AS CHAR(1))
=,CAST(CASE G.INSERTAUTH WHEN 'N' THEN ' ' ELSE 'I' END
= AS CHAR(1))
=,CAST(CASE G.UPDATEAUTH WHEN 'N' THEN ' ' ELSE 'U' END
= AS CHAR(1))
=,CAST(CASE G.DELETEAUTH WHEN 'N' THEN ' ' ELSE 'D' END
= AS CHAR(1))
=,' ' FROM SYSIBM.SYSTABLES T,SYSIBM.SYSTABAUTH G WHERE
= T.CREATOR='?W_SCHEMA?'
= AND (G.GRANTEE LIKE 'I_?_SYSTEM?_?W_REAL_TEST?%' OR G.GRANTEE='PUBLIC')
= AND G.TCREATOR='?W_SCHEMA?' AND T.NAME=G.TTNAME
=}  IF{_DB_ERROR!='I-601'}ERROR{?_DB_ERROR??_ERROR?};
***( VIEW ZZZZ_SYS_COLUMN_VIEW )***
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_SEQ,ZZZZ_DATA_NAME
=,ZZZZ_DEFAULT_TYPE,ZZZZ_DEFAULT_LENGTH1,ZZZZ_DEFAULT_LENGTH2
=,ZZZZ_DEFAULT,ZZZZ_NULL)AS
= SELECT CAST(CASE T.TYPE WHEN 'V'
= THEN 'VIEW' ELSE 'TABLE' END AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(C.TBNAME AS CHAR(30)),COLNO,CAST(C.NAME AS CHAR(30))
=,CAST(CASE C.COLTYPE WHEN 'INTEGER' THEN 'INT' WHEN 'SMALLINT'
= THEN 'INT' WHEN 'TIMESTMP' THEN 'TIMESTAMP' WHEN 'DOUBLE'
= THEN 'FLOAT' ELSE C.COLTYPE END AS CHAR(9))
=,CAST(CASE WHEN C.COLTYPE IN('DECIMAL','CHAR','VARCHAR')
= THEN C.LENGTH WHEN C.COLTYPE IN('TEXT','CLOB') THEN 4000 
= WHEN C.COLTYPE='TIMESTMP' THEN 17 ELSE 9 END AS INT)
=,CAST(CASE WHEN C.COLTYPE IN('DECIMAL') THEN C.SCALE
= ELSE 0 END AS INT)
=,CAST(C.DEFAULT AS CHAR(30)),CAST(CASE C.NULLS WHEN 'N' 
=THEN '0' ELSE '1' END AS CHAR(1)) FROM SYSIBM.SYSCOLUMNS C
=,SYSIBM.SYSTABLES T WHERE
= C.TBNAME=T.NAME AND T.CREATOR='?W_SCHEMA?' 
= AND C.TBCREATOR='?W_SCHEMA?'
=}  IF{_DB_ERROR!='I-601'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}
={(ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_DATA_NAME,ZZZZ_IX,ZZZZ_NO)AS
= SELECT CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3))
=,CAST(TBNAME AS CHAR(30)),CAST(COLNAME AS CHAR(30))
=,CAST(SUBSTR(NAME,2,1) AS CHAR(1))
=,CAST(COLSEQ-CASE WHEN UNIQUERULE<>'D' THEN 1 ELSE 0 END AS CHAR(1))
= FROM SYSIBM.SYSINDEXES INNER JOIN SYSIBM.SYSINDEXCOLUSE
= ON NAME=INDNAME AND CREATOR=INDSCHEMA
= LEFT JOIN SYSIBM.SYSCONSTDEP ON NAME=BNAME
= AND CREATOR=BCREATOR AND DTBCREATOR='?W_SCHEMA?' WHERE
= SUBSTR(NAME,1,2)
= IN('X0','X1','X2','X3','X4','X5','X6','X7','X8','X9')
= AND TBCREATOR='?W_SCHEMA?'
=}  IF{_DB_ERROR!='I-601'}ERROR{?_DB_ERROR??_ERROR?};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=VIEW
=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST'};
=SET{W_PER='ZZZZ'};
COPY=VIEW
=SET{W_PER='ZZZY'};
COPY=VIEW
*********************( FUNCTION )*************************************************
=MESSAGE{CREATE FUNCTION};
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL'};
REPEAT=2
=SET{W_M=_M_?W_REAL_TEST?};
=DBO_DROP_OBJECT{?W_M?_DAY_CHAR_FUNC}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_DAY_CHAR_FUNC(ZZZZ_IN VARCHAR(8),ZZZZ_ADD INT)
= RETURNS VARCHAR(8)
= LANGUAGE SQL
= CONTAINS SQL
= NO EXTERNAL ACTION
= DETERMINISTIC
= RETURN SUBSTR(CHAR(TIMESTAMP(ZZZZ_IN||'000000')+ZZZZ_ADD DAYS),1,4)
=      ||SUBSTR(CHAR(TIMESTAMP(ZZZZ_IN||'000000')+ZZZZ_ADD DAYS),6,2)
=      ||SUBSTR(CHAR(TIMESTAMP(ZZZZ_IN||'000000')+ZZZZ_ADD DAYS),9,2)}
=  IF{_DB_ERROR!='I-454'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_DROP_OBJECT{?W_M?_DAY_DATE_FUNC}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_DAY_DATE_FUNC(ZZZZ_IN TIMESTAMP,ZZZZ_ADD INT)
= RETURNS VARCHAR(8)
= LANGUAGE SQL
= CONTAINS SQL
= NO EXTERNAL ACTION
= DETERMINISTIC
= RETURN SUBSTR(CHAR(ZZZZ_IN+ZZZZ_ADD DAYS),1,4)
=      ||SUBSTR(CHAR(ZZZZ_IN+ZZZZ_ADD DAYS),6,2)
=      ||SUBSTR(CHAR(ZZZZ_IN+ZZZZ_ADD DAYS),9,2)}
=  IF{_DB_ERROR!='I-454'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_DROP_OBJECT{?W_M?_WEEK_CHAR_FUNC}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_WEEK_CHAR_FUNC(ZZZZ_IN VARCHAR(8))
= RETURNS INT
= LANGUAGE SQL
= CONTAINS SQL
= NO EXTERNAL ACTION
= DETERMINISTIC
= RETURN DAYOFWEEK(TIMESTAMP(ZZZZ_IN||'000000'))}
=  IF{_DB_ERROR!='I-454'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_DROP_OBJECT{?W_M?_WEEK_DATE_FUNC}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_WEEK_DATE_FUNC(ZZZZ_IN TIMESTAMP)
= RETURNS INT
= LANGUAGE SQL
= CONTAINS SQL
= NO EXTERNAL ACTION
= DETERMINISTIC
= RETURN DAYOFWEEK(ZZZZ_IN)}
=  IF{_DB_ERROR!='I-454'}ERROR{?_DB_ERROR??_ERROR?};
=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST'};
REPEAT=
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL'};
REPEAT=2
=SET{W_M=_M_?W_REAL_TEST?};
=DBO_SQL{GRANT EXECUTE ON FUNCTION I_?_SYSTEM?_?W_REAL_TEST?_MAST_ZZZZ.ZZZZ_DAY_CHAR_FUNC   TO PUBLIC};
=DBO_SQL{GRANT EXECUTE ON FUNCTION I_?_SYSTEM?_?W_REAL_TEST?_MAST_ZZZZ.ZZZZ_DAY_DATE_FUNC   TO PUBLIC};
=DBO_SQL{GRANT EXECUTE ON FUNCTION I_?_SYSTEM?_?W_REAL_TEST?_MAST_ZZZZ.ZZZZ_WEEK_CHAR_FUNC  TO PUBLIC};
=DBO_SQL{GRANT EXECUTE ON FUNCTION I_?_SYSTEM?_?W_REAL_TEST?_MAST_ZZZZ.ZZZZ_WEEK_DATE_FUNC  TO PUBLIC};
*=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST'};
=SET{W_REAL_TEST='TEST'};
REPEAT=
I-COMMENT_END
