Second visit in enterprise level database software area 🙂 What about simple varchar saving in datafile in an IBM flagship? Let´s see!
1) Preparation
First of all, I will create sample table and fill it with data. There are many ways how to do it. I will present most useful for more than one command. Let´s have a file sample.sql, located in /tmp directory:
connect to sample;
create table acc_test (no smallint,accno varchar (25));
insert into acc_test values (01,'4255-8613-9716-5076');
insert into acc_test values (02,'4531-2930-4967-2232');
insert into acc_test values (03,'4548-7929-1826-4899');
insert into acc_test values (04,'4691-3746-9407-0405');
insert into acc_test values (05,'4649-1103-3295-2551');
insert into acc_test values (06,'4997-5257-8004-9774');
insert into acc_test values (07,'4692-4078-2653-8573');
insert into acc_test values (08,'4044-8832-9347-1412');
insert into acc_test values (09,'4688-8299-4664-4385');
insert into acc_test values (10,'4339-4136-0219-4499');
insert into acc_test values (11,'4016-7845-2950-8098');
insert into acc_test values (12,'4961-6591-9364-9979');
insert into acc_test values (13,'4787-0768-3223-3820');
insert into acc_test values (14,'4582-2114-8843-1397');
insert into acc_test values (15,'4549-3001-8407-0658');
insert into acc_test values (16,'4464-3676-8558-3399');
insert into acc_test values (17,'4804-1261-5366-8044');
insert into acc_test values (18,'4284-1059-8455-5702');
insert into acc_test values (19,'4127-1856-7120-0292');
insert into acc_test values (20,'4633-7926-0863-2443');
commit;
terminate;
Time for running this file:
db2inst1@debian:/home/j$ db2 -tvsf /tmp/sample.sql
connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 10.5.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
create table acc_test (no smallint,accno varchar (25))
DB20000I The SQL command completed successfully.
insert into acc_test values (01,’4255-8613-9716-5076′)
DB20000I The SQL command completed successfully.
insert into acc_test values (02,’4531-2930-4967-2232′)
DB20000I The SQL command completed successfully.
insert into acc_test values (03,’4548-7929-1826-4899′)
DB20000I The SQL command completed successfully.
insert into acc_test values (04,’4691-3746-9407-0405′)
DB20000I The SQL command completed successfully.
insert into acc_test values (05,’4649-1103-3295-2551′)
DB20000I The SQL command completed successfully.
insert into acc_test values (06,’4997-5257-8004-9774′)
DB20000I The SQL command completed successfully.
insert into acc_test values (07,’4692-4078-2653-8573′)
DB20000I The SQL command completed successfully.
insert into acc_test values (08,’4044-8832-9347-1412′)
DB20000I The SQL command completed successfully.
insert into acc_test values (09,’4688-8299-4664-4385′)
DB20000I The SQL command completed successfully.
insert into acc_test values (10,’4339-4136-0219-4499′)
DB20000I The SQL command completed successfully.
insert into acc_test values (11,’4016-7845-2950-8098′)
DB20000I The SQL command completed successfully.
insert into acc_test values (12,’4961-6591-9364-9979′)
DB20000I The SQL command completed successfully.
insert into acc_test values (13,’4787-0768-3223-3820′)
DB20000I The SQL command completed successfully.
insert into acc_test values (14,’4582-2114-8843-1397′)
DB20000I The SQL command completed successfully.
insert into acc_test values (15,’4549-3001-8407-0658′)
DB20000I The SQL command completed successfully.
insert into acc_test values (16,’4464-3676-8558-3399′)
DB20000I The SQL command completed successfully.
insert into acc_test values (17,’4804-1261-5366-8044′)
DB20000I The SQL command completed successfully.
insert into acc_test values (18,’4284-1059-8455-5702′)
DB20000I The SQL command completed successfully.
insert into acc_test values (19,’4127-1856-7120-0292′)
DB20000I The SQL command completed successfully.
insert into acc_test values (20,’4633-7926-0863-2443′)
DB20000I The SQL command completed successfully.
commit
DB20000I The SQL command completed successfully.
terminate
DB20000I The TERMINATE command completed successfully.
db2inst1@debian:/home/j$
db2 => select * from acc_test
NO ACCNO
—— ————————-
1 4255-8613-9716-5076
2 4531-2930-4967-2232
3 4548-7929-1826-4899
4 4691-3746-9407-0405
5 4649-1103-3295-2551
6 4997-5257-8004-9774
7 4692-4078-2653-8573
8 4044-8832-9347-1412
9 4688-8299-4664-4385
10 4339-4136-0219-4499
11 4016-7845-2950-8098
12 4961-6591-9364-9979
13 4787-0768-3223-3820
14 4582-2114-8843-1397
15 4549-3001-8407-0658
16 4464-3676-8558-3399
17 4804-1261-5366-8044
18 4284-1059-8455-5702
19 4127-1856-7120-0292
20 4633-7926-0863-2443
20 record(s) selected.
2) Physical storage
db2 => select TBSPACEID from syscat.tables where TABNAME=’ACC_TEST‘
TBSPACEID
———
3
1 record(s) selected.
As we found tablespace ID where is located our table, we can query exact storage file:
db2 => LIST TABLESPACE CONTAINERS FOR 3
Tablespace Containers for Tablespace 3
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000003/C0000000.LRG
Type = File
Ok, our target file looks nice :
db2inst1@debian:~/db2inst1/NODE0000/SAMPLE/T0000003$ ls -la
total 32780
drwx–x–x 2 db2inst1 db2iadm1 4096 Jan 28 11:32 .
drwx–x–x 8 db2inst1 db2iadm1 4096 Jan 28 16:23 ..
-rw——- 1 db2inst1 db2iadm1 33554432 Jan 28 16:22 C0000000.LRG
-rw——- 1 db2inst1 db2iadm1 0 Jan 28 11:32 .SQLCRT.FLG
3) Security or plain text? 🙂
db2inst1@debian:~/db2inst1/NODE0000/SAMPLE/T0000003$ time strings -a C0000000.LRG | grep „[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}“
4633-7926-0863-2443
4127-1856-7120-0292
4284-1059-8455-5702
4804-1261-5366-8044
4464-3676-8558-3399
4549-3001-8407-0658
4582-2114-8843-1397
4787-0768-3223-3820
4961-6591-9364-9979
4016-7845-2950-8098
4339-4136-0219-4499
4688-8299-4664-4385
4044-8832-9347-1412
4692-4078-2653-8573
4997-5257-8004-9774
4649-1103-3295-2551
4691-3746-9407-0405
4548-7929-1826-4899
4531-2930-4967-2232
4255-8613-9716-5076
real 0m0.566s
user 0m0.284s
sys 0m0.016s
Similar to Oracle – in default storage, no binary saving, no encryption, just plain text. Inspiring for data files hunters, right? 🙂
-a-