home *** CD-ROM | disk | FTP | other *** search
/ PC Treasures, Inc. / pctreasures.mdf / WINDOWS / adabas / f_0001 / env / odbc.ins < prev    next >
Text File  |  1999-11-01  |  15KB  |  535 lines
  1. SAY --- Installing system tables for ODBC ...
  2. /
  3. /* @(#)ODBC.ins 11.00.09  1999-06-09
  4. /
  5. INIT SERVERDB
  6. /
  7. /
  8. / *******************************************************
  9. / Systemtables of the Conversational Component ODBC 2.x
  10. / *******************************************************
  11. /
  12. / ********************************
  13. /  Table SYSODBCTYPES
  14. / ********************************
  15. /
  16. /
  17. IF $RC (EXISTS TABLE SYSODBCTYPES) = 0
  18. THEN
  19.      BEGIN
  20. /
  21.      DROP TABLE SYSODBCTYPES
  22. /
  23.      COMMIT WORK
  24. /
  25.      END
  26. /
  27. CREATE TABLE SYSODBCTYPES
  28.     (
  29.       "TYPE_NAME"        VARCHAR(18) ASCII NOT NULL,
  30.       "DATA_TYPE"        SMALLINT NOT NULL,
  31.       "PRECISION"        INTEGER,
  32.       "LITERAL_PREFIX"        VARCHAR(3) ASCII,
  33.       "LITERAL_SUFFIX"        VARCHAR(3) ASCII,
  34.       "CREATE_PARAMS"        VARCHAR(128) ASCII,
  35.       "NULLABLE"        SMALLINT NOT NULL,
  36.       "CASE_SENSITIVE"        SMALLINT NOT NULL,
  37.       "SEARCHABLE"         SMALLINT NOT NULL,
  38.       "UNSIGNED_ATTRIBUTE"    SMALLINT,
  39.       "MONEY"             SMALLINT NOT NULL,
  40.       "AUTO_INCREMENT"        SMALLINT,
  41.       "LOCAL_TYPE_NAME"    VARCHAR(18) ASCII,
  42.       "MINIMUM_SCALE"        SMALLINT,
  43.       "MAXIMUM_SCALE"        SMALLINT,
  44.       "REMARKS"         VARCHAR(254) ASCII
  45.     )
  46. /
  47. GRANT SELECT ON SYSODBCTYPES TO PUBLIC
  48. /
  49. DATALOAD TABLE SYSODBCTYPES
  50. "TYPE_NAME" 1-1
  51. "DATA_TYPE" 2-2
  52. "PRECISION" 3-3 DEFAULT NULL
  53. "LITERAL_PREFIX" 4-4 DEFAULT NULL
  54. "LITERAL_SUFFIX" 5-5 DEFAULT NULL
  55. "CREATE_PARAMS" 6-6 DEFAULT NULL
  56. "NULLABLE" 7-7
  57. "CASE_SENSITIVE" 8-8
  58. "SEARCHABLE" 9-9
  59. "UNSIGNED_ATTRIBUTE" 10-10 DEFAULT NULL
  60. "MONEY" 11-11
  61. "AUTO_INCREMENT" 12-12 DEFAULT NULL
  62. "LOCAL_TYPE_NAME" 13-13 DEFAULT NULL
  63. "MINIMUM_SCALE"    14-14 DEFAULT NULL
  64. "MAXIMUM_SCALE"    15-15 DEFAULT NULL
  65. "REMARKS" 16-16 DEFAULT NULL
  66. INFILE *
  67. DEC '/ /./'
  68. DATE 'INTERNAL'
  69. TIME 'INTERNAL'
  70. NULL '?                   '
  71. COMPRESS SEPARATOR '|'
  72. /
  73. CHAR|1|254|'|'|max length|1|1|3|?|0|?|CHAR|?|?|?
  74. DECIMAL|2|18|?|?|precision,scale|1|0|2|0|0|?|NUMERIC|0|18|?
  75. FIXED|3|18|?|?|precision,scale|1|0|2|0|0|?|DECIMAL|0|18|?
  76. INTEGER|4|10|?|?|?|1|0|2|0|0|?|INTEGER|?|?|?
  77. SMALLINT|5|5|?|?|?|1|0|2|0|0|?|SMALLINT|?|?|?
  78. FLOAT|6|15|?|?|precision|1|0|2|0|0|?|FLOAT|?|?|?
  79. REAL|7|15|?|?|precision|1|0|2|0|0|?|REAL|?|?|?
  80. DOUBLE PRECISION|8|16|?|?|?|1|0|2|0|0|?|DOUBLE PRECISION|?|?|?
  81. DATE|9|10|'|'|?|1|0|2|?|0|?|DATE|?|?|?
  82. TIME|10|8|'|'|?|1|0|2|?|0|?|TIME|?|?|?
  83. VARCHAR|12|4000|'|'|max length|1|1|3|?|0|?|VARCHAR|?|?|?
  84. LONG|-1|2147483647|'|'|?|1|1|3|?|0|?|LONG|?|?|?
  85. CHAR() BYTE|-2|254|0X|?|max length|1|0|2|?|0|?|BINARY|?|?|?
  86. VARCHAR() BYTE|-3|4000|0X|?|max length|1|0|2|?|0|?|VARBINARY|?|?|?
  87. LONG BYTE|-4|2147483647|?|?|?|1|0|2|?|0|?|LONG BYTE|?|?|?
  88. BOOLEAN|-7|1|?|?|?|1|0|2|?|0|?|BOOLEAN|?|?|?
  89. TIMESTAMP|11|19|'|'|?|1|0|2|?|0|?|TIMESTAMP|?|?|?
  90. /
  91. COMMIT WORK
  92. /
  93. /
  94. / ********************************
  95. /  View SYSODBCINDEXSTAT
  96. / ********************************
  97. /
  98. CREATE OR REPLACE VIEW SYSODBCINDEXSTAT
  99.     AS SELECT
  100.        OWNER, TABLENAME, INDEXNAME, COLUMNNAME, ROWS, PAGECOUNT
  101.        FROM SYSDD.ODBC_INDEXSTATS
  102.     INTERNAL
  103. /
  104. GRANT SELECT ON SYSODBCINDEXSTAT TO PUBLIC
  105. /
  106. /
  107. / ********************************
  108. /  View SYSODBCTABLESTAT
  109. / ********************************
  110. /
  111. CREATE OR REPLACE VIEW SYSODBCTABLESTAT
  112.     AS SELECT
  113.        OWNER, TABLENAME, ROWS, PAGECOUNT
  114.     FROM SYSDD.ODBC_TABLESTATS
  115.     INTERNAL
  116. /
  117. GRANT SELECT ON SYSODBCTABLESTAT TO PUBLIC
  118. /
  119. /
  120. / ********************************
  121. /  View SYSODBCINDEXES
  122. / ********************************
  123. /
  124. CREATE OR REPLACE VIEW SYSODBCINDEXES
  125.       (TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, NON_UNIQUE,
  126.        INDEX_QUALIFIER, INDEX_NAME, TYPE, SEQ_IN_INDEX, COLUMN_NAME,
  127.        COLLATION, CARDINALITY, PAGES, FILTER_CONDITION, INDEX_TYPE)
  128.         AS SELECT
  129.        NULL, OWNER, TABLENAME, NUM(DECODE(TYPE, 'UNIQUE', 0, 1)),
  130.        DECODE(INDEXNAME, 'SYSPRIMARYKEYINDEX', ' ', DECODE(INDEXNAME, ' ', TABLENAME, NULL)),
  131.            DECODE(INDEXNAME,' ', COLUMNNAME,INDEXNAME), 3, COLUMNNO, COLUMNNAME, SUBSTR(SORT, 1, 1),
  132.        NUM(NULL), NUM(NULL), NULL, TYPE
  133.         FROM SYSDD.INDEXES
  134.     INTERNAL
  135. /
  136. GRANT SELECT ON SYSODBCINDEXES TO PUBLIC
  137. /
  138. /
  139. /
  140. / *******************************************************
  141. / Systemtables of the Conversational Component ODBC 3.x
  142. / *******************************************************
  143. /
  144. /
  145. / ********************************
  146. /  Table SYSODBCTYPES3
  147. / ********************************
  148. /
  149. /
  150. IF $RC (EXISTS TABLE SYSODBCTYPES3) = 0
  151. THEN
  152.      BEGIN
  153. /
  154.      DROP TABLE SYSODBCTYPES3
  155. /
  156.      COMMIT WORK
  157. /
  158.      END
  159. /
  160. CREATE TABLE SYSODBCTYPES3
  161.     (
  162.       "TYPE_NAME"        VARCHAR(18) ASCII NOT NULL,
  163.       "DATA_TYPE"        SMALLINT NOT NULL,
  164.       "COLUMN_SIZE"        INTEGER,
  165.       "LITERAL_PREFIX"        VARCHAR(3) ASCII,
  166.       "LITERAL_SUFFIX"        VARCHAR(3) ASCII,
  167.       "CREATE_PARAMS"        VARCHAR(128) ASCII,
  168.       "NULLABLE"        SMALLINT NOT NULL,
  169.       "CASE_SENSITIVE"        SMALLINT NOT NULL,
  170.       "SEARCHABLE"             SMALLINT NOT NULL,
  171.       "UNSIGNED_ATTRIBUTE"    SMALLINT,
  172.       "FIXED_PREC_SCALE"     SMALLINT NOT NULL,
  173.       "AUTO_UNIQUE_VALUE"    SMALLINT,
  174.       "LOCAL_TYPE_NAME"        VARCHAR(18) ASCII,
  175.       "MINIMUM_SCALE"        SMALLINT,
  176.       "MAXIMUM_SCALE"        SMALLINT,
  177.       "SQL_DATA_TYPE"        SMALLINT NOT NULL,
  178.       "SQL_DATETIME_SUB"    SMALLINT,
  179.       "NUM_PREC_RADIX"          INTEGER,
  180.       "INTERVAL_PRECISION"      SMALLINT,
  181.       "REMARKS"         VARCHAR(254) ASCII
  182.     )
  183. /
  184. GRANT SELECT ON SYSODBCTYPES3 TO PUBLIC
  185. /
  186. DATALOAD TABLE SYSODBCTYPES3
  187. "TYPE_NAME" 1-1
  188. "DATA_TYPE" 2-2
  189. "COLUMN_SIZE" 3-3 DEFAULT NULL
  190. "LITERAL_PREFIX" 4-4 DEFAULT NULL
  191. "LITERAL_SUFFIX" 5-5 DEFAULT NULL
  192. "CREATE_PARAMS" 6-6 DEFAULT NULL
  193. "NULLABLE" 7-7
  194. "CASE_SENSITIVE" 8-8
  195. "SEARCHABLE" 9-9
  196. "UNSIGNED_ATTRIBUTE" 10-10 DEFAULT NULL
  197. "FIXED_PREC_SCALE" 11-11
  198. "AUTO_UNIQUE_VALUE" 12-12 DEFAULT NULL
  199. "LOCAL_TYPE_NAME" 13-13 DEFAULT NULL
  200. "MINIMUM_SCALE"    14-14 DEFAULT NULL
  201. "MAXIMUM_SCALE"    15-15 DEFAULT NULL
  202. "SQL_DATA_TYPE" 16-16
  203. "SQL_DATETIME_SUB" 17-17 DEFAULT NULL
  204. "NUM_PREC_RADIX" 18-18 DEFAULT NULL
  205. "INTERVAL_PRECISION" 19-19 DEFAULT NULL
  206. "REMARKS" 20-20 DEFAULT NULL
  207. INFILE *
  208. DEC '/ /./'
  209. DATE 'INTERNAL'
  210. TIME 'INTERNAL'
  211. NULL '?                   '
  212. COMPRESS SEPARATOR '|'
  213. /
  214. CHAR|1|254|'|'|max length|1|1|3|?|0|?|CHAR|?|?|1|?|?|?|?
  215. DECIMAL|2|18|?|?|precision,scale|1|0|2|0|0|?|NUMERIC|0|18|2|?|10|?|?
  216. FIXED|3|18|?|?|precision,scale|1|0|2|0|0|?|DECIMAL|0|18|3|?|10|?|?
  217. INTEGER|4|10|?|?|?|1|0|2|0|0|?|INTEGER|?|?|4|?|10|?|?
  218. SMALLINT|5|5|?|?|?|1|0|2|0|0|?|SMALLINT|?|?|5|?|10|?|?
  219. FLOAT|6|15|?|?|precision|1|0|2|0|0|?|FLOAT|?|?|6|?|10|?|?
  220. REAL|7|15|?|?|precision|1|0|2|0|0|?|REAL|?|?|7|?|10|?|?
  221. DOUBLE PRECISION|8|16|?|?|?|1|0|2|0|0|?|DOUBLE PRECISION|?|?|8|?|10|?|?
  222. DATE|9|10|'|'|?|1|0|2|?|0|?|DATE|?|?|9|1|10|?|?
  223. TIME|10|8|'|'|?|1|0|2|?|0|?|TIME|?|?|9|2|10|?|?
  224. TIMESTAMP|11|19|'|'|?|1|0|2|?|0|?|TIMESTAMP|?|?|9|3|10|?|?
  225. VARCHAR|12|4000|'|'|max length|1|1|3|?|0|?|VARCHAR|?|?|12|?|?|?|?
  226. LONG|-1|2147483647|'|'|?|1|1|3|?|0|?|LONG|?|?|-1|?|?|?|?
  227. CHAR() BYTE|-2|254|0X|?|max length|1|0|2|?|0|?|BINARY|?|?|-2|?|?|?|?
  228. VARCHAR() BYTE|-3|4000|0X|?|max length|1|0|2|?|0|?|VARBINARY|?|?|-3|?|?|?|?
  229. LONG BYTE|-4|2147483647|?|?|?|1|0|2|?|0|?|LONG BYTE|?|?|-4|?|?|?|?
  230. BOOLEAN|-7|1|?|?|?|1|0|2|?|0|?|BOOLEAN|?|?|-7|?|?|?|?
  231. /
  232. COMMIT WORK
  233. /
  234. /
  235. / ********************************
  236. /  View SYSODBTABLES3
  237. / ********************************
  238. /
  239. IF $RC (EXISTS TABLE SYSODBCTABLES3) = 0
  240. THEN
  241.      BEGIN
  242. /
  243.      DROP VIEW SYSODBCTABLES3
  244. /
  245.      COMMIT WORK
  246. /
  247.      END
  248. /
  249. /
  250. CREATE VIEW SYSODBCTABLES3
  251.       (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,  REMARKS)
  252.        AS SELECT
  253.        NULL, OWNER, TABLENAME, DECODE(TYPE,'SYSTEM',
  254.        'SYSTEM TABLE',TYPE), NULL
  255.     FROM "SYSDD".TABLES
  256.        INTERNAL
  257. /
  258. GRANT SELECT ON SYSODBCTABLES3 TO PUBLIC
  259. /
  260. /
  261. / ********************************
  262. /  View SYSODBCCOLUMNS3
  263. / ********************************
  264. /
  265. IF $RC (EXISTS TABLE SYSODBCCOLUMNS3) = 0
  266. THEN
  267.      BEGIN
  268. /
  269.      DROP VIEW SYSODBCCOLUMNS3
  270. /
  271.      COMMIT WORK
  272. /
  273.      END
  274. /
  275. /
  276. CREATE VIEW SYSODBCCOLUMNS3
  277.       (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
  278.        DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS,
  279.        NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE,
  280.        SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE,
  281.        POS, CODETYPE, KEY_SEQ)
  282.     AS SELECT
  283.            NULL, OWNER, TABLENAME, COLUMNNAME, NUM(99), DATATYPE, PRECISION,
  284.            LEN, DEC, RADIX, NULLABLE, NULL, NULL, NUM(99), NULL, LEN,
  285.        COLUMNNO, ASCII(DECODE(NULLABLE,1,'YES',2,'NO','NO')),
  286.        COLUMNNO, CODE, KEY_SEQ
  287.     FROM SYSDD.ODBC_COLUMNS
  288.     INTERNAL
  289. /
  290. GRANT SELECT ON SYSODBCCOLUMNS3 TO PUBLIC
  291. /
  292. /
  293. / ********************************
  294. /  View SYSODBCINDEXSTAT3
  295. / ********************************
  296. /
  297. IF $RC (EXISTS TABLE SYSODBCINDEXSTAT3) = 0
  298. THEN
  299.      BEGIN
  300. /
  301.      DROP VIEW SYSODBCINDEXSTAT3
  302. /
  303.      COMMIT WORK
  304. /
  305.      END
  306. /
  307. /
  308. CREATE VIEW SYSODBCINDEXSTAT3
  309.     AS SELECT
  310.        OWNER, TABLENAME, INDEXNAME, COLUMNNAME, ROWS, PAGECOUNT
  311.        FROM SYSDD.ODBC_INDEXSTATS
  312.     INTERNAL
  313. /
  314. GRANT SELECT ON SYSODBCINDEXSTAT3 TO PUBLIC
  315. /
  316. /
  317. / ********************************
  318. /  View SYSODBCTABLESTAT3
  319. / ********************************
  320. /
  321. IF $RC (EXISTS TABLE SYSODBCTABLESTAT3) = 0
  322. THEN
  323.      BEGIN
  324. /
  325.      DROP VIEW SYSODBCTABLESTAT3
  326. /
  327.      COMMIT WORK
  328. /
  329.      END
  330. /
  331. /
  332. CREATE VIEW SYSODBCTABLESTAT3
  333.     AS SELECT
  334.        OWNER, TABLENAME, ROWS, PAGECOUNT
  335.     FROM SYSDD.ODBC_TABLESTATS
  336.     INTERNAL
  337. /
  338. GRANT SELECT ON SYSODBCTABLESTAT3 TO PUBLIC
  339. /
  340. /
  341. / ********************************
  342. /  View SYSODBCINDEXES3
  343. / ********************************
  344. /
  345. IF $RC (EXISTS TABLE SYSODBCINDEXES3) = 0
  346. THEN
  347.      BEGIN
  348. /
  349.      DROP VIEW SYSODBCINDEXES3
  350. /
  351.      COMMIT WORK
  352. /
  353.      END
  354. /
  355. /
  356. CREATE VIEW SYSODBCINDEXES3
  357.       (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, NON_UNIQUE,
  358.        INDEX_QUALIFIER, INDEX_NAME, TYPE, ORDINAL_POSITION, COLUMN_NAME,
  359.        ASC_OR_DESC, CARDINALITY, PAGES, FILTER_CONDITION, INDEX_TYPE)
  360.         AS SELECT
  361.        NULL, OWNER, TABLENAME, NUM(DECODE(TYPE, 'UNIQUE', 0, 1)),
  362.        DECODE(INDEXNAME, ' ', TABLENAME, NULL), DECODE(INDEXNAME,' ',
  363.        COLUMNNAME,INDEXNAME), 3, COLUMNNO, COLUMNNAME, SUBSTR(SORT, 1, 1),
  364.        NUM(NULL), NUM(NULL), NULL, TYPE
  365.         FROM SYSDD.INDEXES
  366.     INTERNAL
  367. /
  368. GRANT SELECT ON SYSODBCINDEXES3 TO PUBLIC
  369. /
  370. /
  371. / ********************************
  372. /  View SYSODBCFOREIGNKEY3
  373. / ********************************
  374. /
  375. IF $RC (EXISTS TABLE SYSODBCFOREIGNKEY3) = 0
  376. THEN
  377.      BEGIN
  378. /
  379.      DROP VIEW SYSODBCFOREIGNKEY3
  380. /
  381.      COMMIT WORK
  382. /
  383.      END
  384. /
  385. /
  386. CREATE VIEW SYSODBCFOREIGNKEY3
  387.       (PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME,
  388.        FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME,
  389.        KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME,
  390.        DEFERRABILITY)
  391.     AS SELECT
  392.        NULL, REFOWNER, REFTABLENAME, REFCOLUMNNAME, NULL, OWNER,
  393.        TABLENAME, COLUMNNAME, KEYCOLUMNNO, NULL, NUM(DECODE(RULE,
  394.        'DELETE CASCADE',0,'DELETE RESTRICT',1,'DELETE SET NULL',2,2)),
  395.        REFNAME, NULL, NULL
  396.     FROM SYSDD.FOREIGN_KEYS
  397.        INTERNAL
  398. /
  399. GRANT SELECT ON SYSODBCFOREIGNKEY3 TO PUBLIC
  400. /
  401. /
  402. / ********************************
  403. /  View SYSODBCTABLEPRIV3
  404. / ********************************
  405. /
  406. IF $RC (EXISTS TABLE SYSODBCTABLEPRIV3) = 0
  407. THEN
  408.      BEGIN
  409. /
  410.      DROP VIEW SYSODBCTABLEPRIV3
  411. /
  412.      COMMIT WORK
  413. /
  414.      END
  415. /
  416. /
  417. CREATE VIEW SYSODBCTABLEPRIV3
  418.       (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, GRANTOR, GRANTEE,
  419.        PRIVILEGE, IS_GRANTABLE)
  420.     AS SELECT
  421.        NULL, OWNER, NAME1, GRANTOR, GRANTEE, DECODE(SUBSTR(PRIVILEGES,
  422.        LENGTH(PRIVILEGES),1),'+',SUBSTR(PRIVILEGES,1,LENGTH(PRIVILEGES)-1),
  423.        PRIVILEGES), DECODE(SUBSTR(PRIVILEGES,LENGTH(PRIVILEGES),1),'+',
  424.        'YES','NO')
  425.         FROM SYSDD.NEW_PRIVILEGES
  426.         WHERE
  427.        NAME2 IS NULL AND TYPE NOT IN ('SEQUENCE', 'DBPROC')
  428.     INTERNAL
  429. /
  430. GRANT SELECT ON SYSODBCTABLEPRIV3 TO PUBLIC
  431. /
  432. /
  433. / ********************************
  434. /  View SYSODBCCOLUMNPRIV3
  435. / ********************************
  436. /
  437. IF $RC (EXISTS TABLE SYSODBCCOLUMNPRIV3) = 0
  438. THEN
  439.      BEGIN
  440. /
  441.      DROP VIEW SYSODBCCOLUMNPRIV3
  442. /
  443.      COMMIT WORK
  444. /
  445.      END
  446. /
  447. /
  448. CREATE VIEW SYSODBCCOLUMNPRIV3
  449.       (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, GRANTOR,
  450.        GRANTEE, PRIVILEGE, IS_GRANTABLE)
  451.     AS SELECT
  452.            NULL, C.OWNER, C.TABLENAME, C.COLUMNNAME, P.GRANTOR, P.GRANTEE,
  453.            ASCII (DECODE (SUBSTR(P.PRIVILEGES, LENGTH(P.PRIVILEGES), 1), '+',
  454.               SUBSTR (P.PRIVILEGES, 1, LENGTH(P.PRIVILEGES) - 1), P.PRIVILEGES)),
  455.            ASCII (DECODE (SUBSTR(P.PRIVILEGES, LENGTH(P.PRIVILEGES), 1), '+',
  456.               'YES', 'NO'))
  457.         FROM  SYSDD.NEW_PRIVILEGES P, SYSDD.COLUMNS C
  458.         WHERE
  459.        P.TYPE NOT IN ('SEQUENCE', 'DBPROC')        AND
  460.            C.OWNER = P.OWNER AND C.TABLENAME = P.NAME1 AND
  461.            (C.COLUMNNAME = P.NAME2 OR P.NAME2 IS NULL)
  462.         INTERNAL
  463. /
  464. GRANT SELECT ON SYSODBCCOLUMNPRIV3 TO PUBLIC
  465. /
  466. /
  467. / ********************************
  468. /  View SYSODBCPROCEDURE3
  469. / ********************************
  470. /
  471. IF $RC (EXISTS TABLE SYSODBCPROCEDURE3) = 0
  472. THEN
  473.      BEGIN
  474. /
  475.      DROP VIEW SYSODBCPROCEDURE3
  476. /
  477.      COMMIT WORK
  478. /
  479.      END
  480. /
  481. /
  482. CREATE VIEW SYSODBCPROCEDURE3
  483.       (PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME,
  484.        NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS, NUM_RESULT_SETS, REMARKS,
  485.        PROCEDURE_TYPE)
  486.     AS SELECT
  487.        NULL, OWNER, DBPROCEDURE, NULL, NULL, NULL, COMMENT, NUM(1)
  488.     FROM SYSDD.DBPROCEDURES
  489.     WHERE
  490.        DBPROCEDURE <> ' '
  491.        INTERNAL
  492. /
  493. GRANT SELECT ON SYSODBCPROCEDURE3 TO PUBLIC
  494. /
  495. /
  496. / ********************************
  497. /  View SYSODBCPROCCOLUMN3
  498. / ********************************
  499. /
  500. IF $RC (EXISTS TABLE SYSODBCPROCCOLUMN3) = 0
  501. THEN
  502.      BEGIN
  503. /
  504.      DROP VIEW SYSODBCPROCCOLUMN3
  505. /
  506.      COMMIT WORK
  507. /
  508.      END
  509. /
  510. /
  511. CREATE VIEW SYSODBCPROCCOLUMN3
  512.       (PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, COLUMN_NAME,
  513.        COLUMN_TYPE, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH,
  514.        DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEF,
  515.        SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
  516.        ORDINAL_POSITION, IS_NULLABLE, CODETYPE)
  517.     AS SELECT
  518.        NULL, OWNER, DBPROCEDURE, PARAMETERNAME, NUM(DECODE("IN/OUT-TYPE",
  519.        'IN',1,'IN/OUT',2,'   OUT',4,0)), NUM(99), DATATYPE, LEN,
  520.        NUM(DECODE(DATATYPE,'FIXED',LEN+2,'FLOAT',DECODE((LEN DIV 8+1)*
  521.        4,4,4,8,8,12,8),'INTEGER',4,'SMALLINT',2,LEN)), DEC, DECODE(
  522.        DATATYPE,'FIXED',10,'FLOAT',10,'INTEGER',10,'SMALLINT',10,NULL),
  523.        NUM(1), NULL, NULL, NUM(99), NULL, LEN, PARAM_NO, ASCII('YES'), CODE
  524.     FROM SYSDD.DBPROCPARAMETERS
  525.     WHERE
  526.        DBPROCEDURE <> ' '
  527.     INTERNAL
  528. /
  529. GRANT SELECT ON SYSODBCPROCCOLUMN3 TO PUBLIC
  530. /
  531. /
  532. END INIT SERVERDB
  533. /
  534.  
  535.