[DERBY-7086] Derby opera DB growing fast

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.8.1.2, 10.8.2.2, 10.8.3.0
• Component/s: SQL
• Environment: Derby on CentOS8
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Gobi
• Created: 15/Sep/20 04:28
• Updated: 10/Dec/20 15:41

Description

Hi Team,
 
Greetings to you!
 
We use Derby database version 10.8
 
Our observation is that there is not much table data but the disk size is too large.  Large space occupied by a /log folder .  We would like to check with Derby team what this could be holding.
 
We replaced the customer Opera DB, Our analysis given below which is before and after replacement.

BEFORE opening in ij.sh
[g0-7540c/Standalone od-issue]# du -h var
48K     var/db/opera/files/B88A9E5185C464BF6DEC15CEE45E40AC/B3C4E02E5379FEBA0FBB40DB4FE840C0
52K     var/db/opera/files/B88A9E5185C464BF6DEC15CEE45E40AC
4.0K    var/db/opera/files/AFA9712BE7E8196E70F62B08ABDE44B9
4.0K    var/db/opera/files/9FDC6EC9F5ECC99A86E8B36E23BA42A8
4.0K    var/db/opera/files/9876690C43C63886472E8093CC5040F6
4.0K    var/db/opera/files/94D70757CE7B1A374A8D2F05178E48DF
40K     var/db/opera/files/BDE828C23CFC92DCD76249734B9B424C/A3D7B6B3515E1E97E58CDE057B664EBF
44K     var/db/opera/files/BDE828C23CFC92DCD76249734B9B424C
4.0K    var/db/opera/files/94C9A9659E044849CE9CA52E58E74E0D
384K    var/db/opera/files/A0A1F400944A7444460F0345B18540CE/AF0E0325DECF1EB322DECCA2CC9A4D2E
388K    var/db/opera/files/A0A1F400944A7444460F0345B18540CE
40K     var/db/opera/files/92AFC3101442985C97DB95407FD34F1E/8AAA75548D79A21EC53680FBB23443FB
44K     var/db/opera/files/92AFC3101442985C97DB95407FD34F1E
552K    var/db/opera/files
4.0K    var/db/opera/derby/tmp
11M     var/db/opera/derby/seg0
626M    var/db/opera/derby/log
636M    var/db/opera/derby
637M    var/db/opera
637M    var/db
637M    var

[g0-7540c/Standalone od-issue]# cd var
[g0-7540c/Standalone var]# cd db

[g0-7540c/Standalone db]# ij.sh
ij version 10.8
ij> connect 'jdbc:derby:opera/derby';
ij> quit;
[g0-7540c/Standalone db]# cd ../..

AFTER
[g0-7540c/Standalone od-issue]# du -h var
48K     var/db/opera/files/B88A9E5185C464BF6DEC15CEE45E40AC/B3C4E02E5379FEBA0FBB40DB4FE840C0
52K     var/db/opera/files/B88A9E5185C464BF6DEC15CEE45E40AC
4.0K    var/db/opera/files/AFA9712BE7E8196E70F62B08ABDE44B9
4.0K    var/db/opera/files/9FDC6EC9F5ECC99A86E8B36E23BA42A8
4.0K    var/db/opera/files/9876690C43C63886472E8093CC5040F6
4.0K    var/db/opera/files/94D70757CE7B1A374A8D2F05178E48DF
40K     var/db/opera/files/BDE828C23CFC92DCD76249734B9B424C/A3D7B6B3515E1E97E58CDE057B664EBF
44K     var/db/opera/files/BDE828C23CFC92DCD76249734B9B424C
4.0K    var/db/opera/files/94C9A9659E044849CE9CA52E58E74E0D
384K    var/db/opera/files/A0A1F400944A7444460F0345B18540CE/AF0E0325DECF1EB322DECCA2CC9A4D2E
388K    var/db/opera/files/A0A1F400944A7444460F0345B18540CE
40K     var/db/opera/files/92AFC3101442985C97DB95407FD34F1E/8AAA75548D79A21EC53680FBB23443FB
44K     var/db/opera/files/92AFC3101442985C97DB95407FD34F1E
552K    var/db/opera/files
11M     var/db/opera/derby/seg0
1.1M    var/db/opera/derby/log
12M     var/db/opera/derby
12M     var/db/opera
12M     var/db
12M     var

[g0-7540c/Standalone db]# ij.sh
ij version 10.8
ij> connect 'jdbc:derby:opera/derby';
ij> show tables;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS
------------------------------------------------------------------------
SYS                 |SYSALIASES                    |
SYS                 |SYSCHECKS                     |
SYS                 |SYSCOLPERMS                   |
SYS                 |SYSCOLUMNS                    |
SYS                 |SYSCONGLOMERATES              |
SYS                 |SYSCONSTRAINTS                |
SYS                 |SYSDEPENDS                    |
SYS                 |SYSFILES                      |
SYS                 |SYSFOREIGNKEYS                |
SYS                 |SYSKEYS                       |
SYS                 |SYSPERMS                      |
SYS                 |SYSROLES                      |
SYS                 |SYSROUTINEPERMS               |
SYS                 |SYSSCHEMAS                    |
SYS                 |SYSSEQUENCES                  |
SYS                 |SYSSTATEMENTS                 |
SYS                 |SYSSTATISTICS                 |
SYS                 |SYSTABLEPERMS                 |
SYS                 |SYSTABLES                     |
SYS                 |SYSTRIGGERS                   |
SYS                 |SYSVIEWS                      |
SYSIBM              |SYSDUMMY1                     |
APP                 |CONTEXT_HOLDER                |
APP                 |CONTEXT_VALUE                 |
APP                 |GLOBAL_CONTEXT_VALUE          |
APP                 |REF_COUNT                     |
APP                 |VARIABLE                      |
APP                 |XML_BINARY                    |
APP                 |XML_DOCUMENT                  |
APP                 |XML_STRING                    |

30 rows selected
ij> select count from APP.CONTEXT_HOLDER;
1
-----------
255

1 row selected
ij> select count from APP.CONTEXT_VALUE;
1
-----------
160

1 row selected
ij> select count from GLOBAL_CONTEXT_VALUE;
1
-----------
0

1 row selected
ij> select count from REF_COUNT;
1
-----------
18

1 row selected
ij> select count from VARIABLE;
1
-----------
65

1 row selected
ij> select count from XML_BINARY;
1
-----------
0

1 row selected
ij> select count from XML_DOCUMENT;
1
-----------
18

1 row selected
ij> select count from XML_STRING;
1
-----------
35

1 row selected
ij> quit;
 
Please let us know is there any known issues we have around this issue.
 
Thanks & Regards,
Gobi S,
Cast Iron / App Connect Professional L3 Support,
IBM India pvt Ltd.
Email: [e-mail]