{"id":291,"date":"2014-01-28T15:54:34","date_gmt":"2014-01-28T15:54:34","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=291"},"modified":"2014-01-28T20:31:30","modified_gmt":"2014-01-28T20:31:30","slug":"how-db2-stores-data","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=291","title":{"rendered":"How DB2 stores data"},"content":{"rendered":"

Second visit in enterprise level database software area \ud83d\ude42 What about simple varchar saving in datafile in an IBM flagship? Let\u00b4s see!<\/p>\n

<\/p>\n

1) Preparation<\/strong><\/p>\n

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\u00b4s have a file sample.sql, located in \/tmp directory:
\n
\nconnect to sample;<\/span>
\ncreate table acc_test (no smallint,accno varchar (25));<\/span>
\ninsert into acc_test values (01,'4255-8613-9716-5076');<\/span>
\ninsert into acc_test values (02,'4531-2930-4967-2232');<\/span>
\ninsert into acc_test values (03,'4548-7929-1826-4899');<\/span>
\ninsert into acc_test values (04,'4691-3746-9407-0405');<\/span>
\ninsert into acc_test values (05,'4649-1103-3295-2551');<\/span>
\ninsert into acc_test values (06,'4997-5257-8004-9774');<\/span>
\ninsert into acc_test values (07,'4692-4078-2653-8573');<\/span>
\ninsert into acc_test values (08,'4044-8832-9347-1412');<\/span>
\ninsert into acc_test values (09,'4688-8299-4664-4385');<\/span>
\ninsert into acc_test values (10,'4339-4136-0219-4499');<\/span>
\ninsert into acc_test values (11,'4016-7845-2950-8098');<\/span>
\ninsert into acc_test values (12,'4961-6591-9364-9979');<\/span>
\ninsert into acc_test values (13,'4787-0768-3223-3820');<\/span>
\ninsert into acc_test values (14,'4582-2114-8843-1397');<\/span>
\ninsert into acc_test values (15,'4549-3001-8407-0658');<\/span>
\ninsert into acc_test values (16,'4464-3676-8558-3399');<\/span>
\ninsert into acc_test values (17,'4804-1261-5366-8044');<\/span>
\ninsert into acc_test values (18,'4284-1059-8455-5702');<\/span>
\ninsert into acc_test values (19,'4127-1856-7120-0292');<\/span>
\ninsert into acc_test values (20,'4633-7926-0863-2443');<\/span>
\ncommit;<\/span>
\nterminate;<\/span>
\n<\/code>
\nTime for running this file:<\/p>\n

db2inst1@debian:\/home\/j$ db2 -tvsf \/tmp\/sample.sql<\/strong><\/span><\/span>
\nconnect to sample<\/span><\/p>\n

\u00a0\u00a0 Database Connection Information<\/span><\/p>\n

\u00a0Database server\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = DB2\/LINUXX8664 10.5.0<\/span>
\n\u00a0SQL authorization ID\u00a0\u00a0 = DB2INST1<\/span>
\n\u00a0Local database alias\u00a0\u00a0 = SAMPLE<\/span><\/p>\n

create table acc_test (no smallint,accno varchar (25))<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (01,’4255-8613-9716-5076′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (02,’4531-2930-4967-2232′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (03,’4548-7929-1826-4899′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (04,’4691-3746-9407-0405′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (05,’4649-1103-3295-2551′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (06,’4997-5257-8004-9774′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (07,’4692-4078-2653-8573′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (08,’4044-8832-9347-1412′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (09,’4688-8299-4664-4385′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (10,’4339-4136-0219-4499′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (11,’4016-7845-2950-8098′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (12,’4961-6591-9364-9979′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (13,’4787-0768-3223-3820′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (14,’4582-2114-8843-1397′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (15,’4549-3001-8407-0658′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (16,’4464-3676-8558-3399′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (17,’4804-1261-5366-8044′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (18,’4284-1059-8455-5702′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (19,’4127-1856-7120-0292′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

insert into acc_test values (20,’4633-7926-0863-2443′)<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

commit<\/span>
\nDB20000I\u00a0 The SQL command completed successfully.<\/span><\/p>\n

terminate<\/span>
\nDB20000I\u00a0 The TERMINATE command completed successfully.<\/span><\/p>\n

db2inst1@debian:\/home\/j$ <\/span><\/p>\n

db2 => select * from acc_test<\/strong><\/code><\/span><\/span><\/p>\n

NO\u00a0\u00a0\u00a0\u00a0 ACCNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n—— ————————-<\/span>
\n\u00a0\u00a0\u00a0\u00a0 1 4255-8613-9716-5076\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 2 4531-2930-4967-2232\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 3 4548-7929-1826-4899\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 4 4691-3746-9407-0405\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 5 4649-1103-3295-2551\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 6 4997-5257-8004-9774\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 7 4692-4078-2653-8573\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 8 4044-8832-9347-1412\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0\u00a0 9 4688-8299-4664-4385\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 10 4339-4136-0219-4499\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 11 4016-7845-2950-8098\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 12 4961-6591-9364-9979\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 13 4787-0768-3223-3820\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 14 4582-2114-8843-1397\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 15 4549-3001-8407-0658\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 16 4464-3676-8558-3399\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 17 4804-1261-5366-8044\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 18 4284-1059-8455-5702\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 19 4127-1856-7120-0292\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span>
\n\u00a0\u00a0\u00a0 20 4633-7926-0863-2443\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span><\/p>\n

\u00a0 20 record(s) selected.<\/span><\/p>\n

2) Physical storage<\/strong><\/p>\n

db2 => select TBSPACEID from syscat.tables where TABNAME=’ACC_TEST‘<\/strong><\/span><\/span><\/p>\n

TBSPACEID<\/span>
\n———<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3<\/span><\/p>\n

\u00a0 1 record(s) selected.<\/span><\/p>\n

As we found tablespace ID where is located our table, we can query exact storage file:<\/p>\n

db2 => LIST TABLESPACE CONTAINERS FOR 3<\/strong><\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Tablespace Containers for Tablespace 3<\/span><\/p>\n

\u00a0Container ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 0<\/span>
\n\u00a0Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/home\/db2inst1\/db2inst1\/NODE0000\/SAMPLE\/T0000003\/C0000000.LRG<\/span>
\n\u00a0Type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = File<\/span><\/p>\n

Ok, our target file looks nice :<\/p>\n

db2inst1@debian:~\/db2inst1\/NODE0000\/SAMPLE\/T0000003$ ls -la<\/strong><\/span>
\ntotal 32780
\ndrwx–x–x 2 db2inst1 db2iadm1\u00a0\u00a0\u00a0\u00a0 4096 Jan 28 11:32 .
\ndrwx–x–x 8 db2inst1 db2iadm1\u00a0\u00a0\u00a0\u00a0 4096 Jan 28 16:23 ..
\n-rw——- 1 db2inst1 db2iadm1 33554432 Jan 28 16:22 C0000000.LRG
\n-rw——- 1 db2inst1 db2iadm1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 Jan 28 11:32 .SQLCRT.FLG<\/span><\/p>\n

 <\/p>\n

3) Security or plain text? \ud83d\ude42<\/strong><\/p>\n

db2inst1@debian:~\/db2inst1\/NODE0000\/SAMPLE\/T0000003$ time strings -a C0000000.LRG | grep „[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}\\-[0-9]\\{4\\}“<\/strong><\/span><\/span>
\n4633-7926-0863-2443<\/span>
\n4127-1856-7120-0292<\/span>
\n4284-1059-8455-5702<\/span>
\n4804-1261-5366-8044<\/span>
\n4464-3676-8558-3399<\/span>
\n4549-3001-8407-0658<\/span>
\n4582-2114-8843-1397<\/span>
\n4787-0768-3223-3820<\/span>
\n4961-6591-9364-9979<\/span>
\n4016-7845-2950-8098<\/span>
\n4339-4136-0219-4499<\/span>
\n4688-8299-4664-4385<\/span>
\n4044-8832-9347-1412<\/span>
\n4692-4078-2653-8573<\/span>
\n4997-5257-8004-9774<\/span>
\n4649-1103-3295-2551<\/span>
\n4691-3746-9407-0405<\/span>
\n4548-7929-1826-4899<\/span>
\n4531-2930-4967-2232<\/span>
\n4255-8613-9716-5076<\/span><\/p>\n

real\u00a0\u00a0 \u00a00m0.566s<\/span>
\nuser\u00a0\u00a0 \u00a00m0.284s<\/span>
\nsys\u00a0\u00a0 \u00a00m0.016s<\/span><\/p>\n

Similar to Oracle – in default storage, no binary saving, no encryption, just plain text. Inspiring for data files hunters, right? \ud83d\ude42<\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

Second visit in enterprise level database software area \ud83d\ude42 What about simple varchar saving in datafile in an IBM flagship? Let\u00b4s see!<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,11],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/291"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=291"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/291\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=291"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}