DB2
文章平均质量分 69
假装80后
一个不念过去,不畏将来的程序员
展开
-
Usage of mainframe DB2 trigger
General Trigger Creation Syntax$ cat create_trigger.sql--#SET TERMINATOR #DROP TRIGGER #CREATE TRIGGER AFTER [UPDATE|INSERT|DELETE] ON REFERENCING [NEW|NEW_TABLE] AS N [OL原创 2016-11-04 22:17:17 · 831 阅读 · 0 评论 -
Oracle/DB2 null key index
(Index key is null means all fields in an index are null.)In DB2 only one NULL key may exist in a unique index.When table and index are created asCREATE TABLE TAB ( A DECIMAL(6原创 2016-10-16 12:59:06 · 1178 阅读 · 0 评论 -
DB2 DECIMAL data type
Describe DB2 z/OS DECIMAL data typeUsage FormatDECIMAL(PRECISION, SCALE)PRECISION refers to the maximum number of digits that are present in the number, andSCALE refers to the ma原创 2016-11-11 23:35:59 · 1239 阅读 · 0 评论 -
SQL Function for DB2 and ORA
SQL Function in DB2 9(UDF=user defined function)DB2 9 and earlier, your could only has RETURN statement in your UDF logic. (This is a too limited design)• RETURN can’t contain a SELECT sta原创 2016-11-12 09:15:07 · 398 阅读 · 0 评论 -
DB2 mainframe support UNICODE
1. Create tableCREATE TABLE TAB(ID DECIMAL(4, 0) NOT NULL, NAME CHAR(06) NOT NULL, ADDRESS VARCHAR(10) NOT NULL, EMAIL VARCHAR(06), PHONE CHAR(04), PRIMARY KEY(原创 2016-10-20 22:01:30 · 1094 阅读 · 0 评论 -
DB2 trigger on a view
This article will discuss the trigger on a view, other than a table.Firstly, let's define updatable view, then continue define trigger on view.1. Updatable View Most of, but the simplest,原创 2016-11-18 23:06:28 · 504 阅读 · 0 评论 -
DB2 client connect to server
0. Prerequisite TCP connection must be configured on DB2 server.1. Catalog the TCP/IP node on the DB2 client side: Command:db2 catalog tcpip node remote server Sample: db2 catalog原创 2016-11-19 14:02:53 · 3094 阅读 · 0 评论 -
How to display CLOB column
This article will give samples how database client display CLOB column for DB2 and OracleDB2 clientDB2 client only show first 8K length, the rest are truncated with following WARNING message原创 2016-11-19 14:12:03 · 891 阅读 · 0 评论 -
JDBC insert BLOB column
Insert a BLOB column from a file.Suppose table is defined as: CREATE TABLE TESTTAB(A INT, B BLOB);Oracle JDBC insert BLOB dataimport java.io.File;import java.io.FileInputStream;import java原创 2016-11-19 14:18:18 · 1121 阅读 · 0 评论 -
Oracle SQL function overloading
Sample of implement Oracle SQL function overloading, and default parameter.To define your own to_char(...) function support different kinds of input data type.Please notice they must be defi原创 2016-11-19 14:22:16 · 433 阅读 · 0 评论 -
DB2 SQL function overloading with DECIMAL datatype
DB2 SQL function overloading does not care about datatype length, and precision, for exampleMYFUN(P DECIMAL) and MYFUN(P DECIMAL(6, 2) are conflicted definition; DB2 will not regard them as overload原创 2016-11-19 14:28:42 · 405 阅读 · 0 评论 -
DB2 define external scalar java user-defined function
A sample of DB2 define java external scalar user-defined function.Take md5() function for example:The function will accept a string as input, and return md5 string.Step 0 : verify DB2 java envir原创 2016-11-19 14:49:14 · 853 阅读 · 0 评论 -
DB2 C language external scalar user-defined function
Sample step of define a C language external scalar user-defined function.1. Define function implementation using C language$ cat mymd5.c#include #include #include #include #include #i原创 2016-11-26 08:32:00 · 487 阅读 · 0 评论 -
Generate DB2 table definition COPYBOOK
When accessing DB2 table in a COBOL program, usually we need know the table definition COPYBOOK.Utility DCLGEN could be used to generate this COPYBOOK file.For example: when table was defined原创 2016-11-11 22:55:54 · 668 阅读 · 0 评论 -
JDBC access DB2
Sample program using JDBC access DB2 database.Prerequisite: installing DB2 JDBC driver and license JARsThe DB2 Universal JDBC Driver.1. db2jcc.jar : includes functions in the JDBC 3.0 and ea原创 2016-11-11 22:45:39 · 781 阅读 · 0 评论 -
DB2 system table resource
-- CHECK USER TABLESSELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATEDBY = CURRENT_SCHEMASELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATEDBY = ''-- CHECK TABLE COLUMNS INFORMATIONSELECT NAME,原创 2016-11-11 22:28:51 · 534 阅读 · 0 评论 -
DB2 Passing transition tables from trigger to stored procedure
欢迎使用Markdown编辑器写博客本Markdown编辑器使用StackEdit修改而来,用它写博客,将会带来全新的体验哦:Markdown和扩展Markdown简洁的语法代码块高亮图片链接和图片上传LaTex数学公式UML序列图和流程图离线写博客导入导出Markdown文件丰富的快捷键快捷键加粗 Ctrl + B 斜体 Ctrl + I 引用 Ctrl原创 2016-11-04 22:38:33 · 500 阅读 · 0 评论 -
DB2 BLOB data type
SQL samples of INSERT/SELECT DB2 BLOB data type.db2 => drop table db2 => create table (a varchar(4), b varchar(4) for bit data, c blob(4))db2 => describe select * from Column Inform原创 2016-11-06 20:06:30 · 2406 阅读 · 0 评论 -
DB2 configure unixODBC
Configure UnixODBC to a DB2 V9.7 on a Linux1. configure db2cli.ini$ update /opt/ibm/db2/V9.7/cfg/db2cli.ini[ODBCDB2]Protocol=TCPIP4Port=50000Database=artHostname=192.168.1.189U原创 2016-10-18 21:18:50 · 716 阅读 · 0 评论 -
DB2 client commond line options
Basic DB2 Command Line OptionsThere are options I used most frequently:-t –terminated – the statements are terminated with a delimiter. The default delimiter is the semi-colon-d原创 2016-10-29 08:58:31 · 511 阅读 · 0 评论 -
DB2 client configure autocommit
1. Enable auto commitdb2 => update command options using c onDB20000I The UPDATE COMMAND OPTIONS command completed successfully.2. Disable auto commitdb2 => update command options using c off原创 2016-10-29 09:03:15 · 1146 阅读 · 0 评论 -
DB2 check table definition
1. Get all your table listdb2 => SELECT DISTINCT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = CURRENT SCHEMAordb2 => SELECT DISTINCT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR = CURRENT_SCHEMAor spec原创 2016-10-29 09:16:48 · 570 阅读 · 0 评论 -
DSNTIAUL UNLOAD DB2 TABLE INTO DATASET
DB2 Utility DSNTIAUL can be used to unload a table to a dataset.Suppose table is defined as: CREATE TABLE TAB( ID DECIMAL(4, 0) NOT NULL, NAME原创 2016-10-20 22:03:25 · 1153 阅读 · 0 评论 -
DSNUTILB UNLOAD DB2 TABLE INTO DATASET
Like DSNTIAUL, another utility DSNUTILB can also be used to unload a table into a dataset.//UNUTTAB JOB (DSNTEP4),'UNUTTAB',REGION=0M,CLASS=2,MSGCLASS=X,// NOTIFY=&SYSUID//DEL原创 2016-10-20 22:04:32 · 1016 阅读 · 0 评论 -
DB2 mainframe table operation DML
Sample JCL to operate a DB2 table.//OPERTBL JOB (DSNTEP4),'OPERTBL',REGION=0M,CLASS=2,MSGCLASS=X,// NOTIFY=&SYSUID//*============================================================//STE原创 2016-10-29 09:32:08 · 599 阅读 · 0 评论 -
DB2 mainframe create table DDL
Sample JCL to create DB2 table on mainframe//CREATBL JOB (DSNTEP4),'CREATBL',REGION=0M,CLASS=2,MSGCLASS=X,// NOTIFY=&SYSUID//*=========================================================原创 2016-10-29 09:26:55 · 849 阅读 · 0 评论 -
DB2 how to drop overloaded functions
When you try to drop an overloaded function/procedure, DB2 will report a SQL0476N error code.For example: if function MY_TOCHAR is overloaded withCREATE FUNCTION MY_TOCHAR(P DATE) RETURNS VARCHA原创 2016-11-26 08:36:48 · 1029 阅读 · 0 评论