DATE:
The ISO format date in DB2 is CCYY-MM-DD in the range of 0001-01-01 to 9999-12-31.
COBOL host variable equivalent: PIC X(10)
DB2 supports ISO data format CCYY-MM-DD.
Example:
CREATE TABLE reading_lists(
user_id INT NOT NULL,
book_id INT NOT NULL,
added_on DATE DEFAULT CURRENT_DATE,
PRIMARY KEY(user_id, book_id)
);
INSERT INTO reading_lists(user_id, book_id) VALUES(1,1);
Converting COBOL field data to DB2 host variable format:
Example: 20200514
01
WS-DATE.
05 WS-YEAR PIC X(04).
05 FILLER PIC X VALUE ‘-‘.
05 WS-MONTH PIC X(02).
05 FILLER PIC X VALUE ‘-‘.
05 WS-DAY PIC
X(02).
05 FILLER PIC X VALUE ‘-‘.
MOVE 2020 TO WS-YEAR
MOVE 05 TO WS-MONTH
MOVE 14 TO WS-DAY
MOVE WS-DATE TO WS-HOST-VARIABLE
When date is 0, we need to insert NULL value in date field. Then please code like below
IF WS-DATE = ZEROS
MOVE ‘0001-01-01’ TO WS-HOST-VARIABLE => Moving null
TIME:
The ISO format time in DB2 is HH.MM.SS in the range of 00.00.00 to 24.00.00
Example:
CREATE TABLE daily_routines(
routine_id INT
GENERATED BY DEFAULT AS IDENTITY
NOT NULL PRIMARY KEY,
routine VARCHAR(100) NOT NULL,
start_at TIME NOT NULL
);
INSERT INTO daily_routines(routine, start_at) VALUES
('Get up','06:00'),
('Brush your teeth','06:05'),
('Have breakfast','06:15'),
('Go to school','06:45'),
('Go home','17:00');
COBOL host variable equivalent: PIC X(8).
01 WS-TIME.
05 WS-HOURS PIC X(02).
05 FILLER PIC X VALUE ‘.‘.
05 WS-MINUTES PIC X(02).
05 FILLER PIC X VALUE ‘.‘.
05 WS-SECONDS PIC X(02).
05 FILLER PIC X VALUE ‘.‘.
Converting COBOL field data to DB2 host variable format:
After moving time to ws-time, then
MOVE WS-DATE TO HOST-VARIABLE
TIMESTAMP:
The ISO format time in DB2 is HH.MM.SS in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000
Example:
CREATE TABLE logs(
log_id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
message VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(log_id)
);
INSERT INTO logs(message)
VALUES('Testing timestamp');
COBOL host variable equivalent: PIC X(26).
VARCHAR:
This data type in db2 is used to store VARIABLE-LENGTH character strings.
Syntax: column-name VARCHAR(n)
'n' is positive integer which represents maximum length of bytes a column can store. It should be greater than 0 and less than 32740.
Example:
CREATE TABLE db2_varchars (
v VARCHAR(20) NOT NULL
);
INSERT INTO db2_varchars (v) VALUES ('Db2 Tutorial');
Converting COBOL field data to DB2 host variable format:
VARCHAR field representation in COBOL (provided by DCLGEN) is like below (COBOL host variable equivalent)
01 column-name.
49 column-len pic S9(4) comp.
49 column-text pic x(n).
How to move data to a varchar field in DB2 from COBOL:
Rules:
o Remove padded spaces from the text
o Populate the actual length of the
text
Example: 77 WS-INDI PIC X(7).
77 WS-INDCTR PIC S9(4) COMP.
77 WS-ACT-INDI PIC X(7).
MOVE 'A Z H' TO WS-INDI
INSPECT FUNCTION REVERSE(WS-INDI) TALLYING WS-INDCTR
FOR LEADING SPACES => COUNT THE LEADING SPACES
DISPLAY 'WS-INDCTR1: ' WS-INDCTR
SUBTRACT WS-INDCTR FROM 7 GIVING WS-INDCTR => SUBTRACTING LEADING SPACES FROM THE TOTAL LENGTH AND STORE THE ACTUAL LENGTH (ACTUAL LENGTH-LEADING SPACES)
DISPLAY 'WS-INDCTR2: ' WS-INDCTR
MOVE WS-INDI(1:WS-INDCTR) TO WS-ACT-INDI => MOVING ACTUAL VALUE WITHOUT LEADING SPACES TO HOST-VARIABLE
DISPLAY 'WS-ACT-INDI: ' WS-ACT-IND
Output:
WS-INDCTR1:
+0002
WS-INDCTR2:
+0005
WS-ACT-INDI: A
Z HSMALLINT:
To store small integers in 2 bytes.
Syntax: col-name SMALLINT
COBOL host variable equivalent: S9(4) COMP.
INT:
To store large integers in 4 bytes.
Syntax: col-name INT
COBOL host variable equivalent: S9(9) COMP.
BIGINT:
To store big integers in 8 bytes.
Syntax: col-name INT
COBOL host variable equivalent: S9(18) COMP.
Example:
CREATE TABLE db2_integers(
smallint_col SMALLINT,
int_col INT,
bigint_col BIGINT );
db2_integers represents table-name
INSERT INTO db2_integers (
smallint_col,
int_col,
bigint_col )
VALUES (
32767,
2147483647,
9223372036854775807
);
CHAR:
To store FIXED-LENGTH character strings in database.
Syntax: col-name CHAR(n)
COBOL host variable equivalent: PIC X(n)
Example:
CREATE TABLE db2_characters(
char_col CHAR(3)
);
INSERT INTO db2_characters(char_col)VALUES('abc');
DECIMAL:
To store decimal type values in database.
The decimal number consists of two parts viz. the whole part and fractional part. They both are separated by comma.
Syntax: dec_col DECIMAL(m,n) or
dec_col NUMERIC(m,n)
‘m’ represents whole part, ‘n’ represents fractional part.
ex. DECIMAL(5,3) => 12.345
Example:
CREATE TABLE db2_decimals(
dec_col NUMERIC(5,3)
);
INSERT INTO db2_decimals(dec_col)
VALUES(12.345);
COBOL host variable equivalent: S9(m-n)V(n) COMP-3
