How to Reduce size of dat file

[nekkunj (NEkkunj Pilani)]

There is one dat file under wbis/IntegrationServer/instances/default/db/LWQ_DB_MESSAGING_CSQ/seg0 .
That dat file is occupying size of 4.6 GB. These dat files are associated with derby database and it sows that we cannot delete or modify it. Anyone can help out on how to reduce this size.

[Venkata_Kasi_Viswanath_Mugada1 (Kasi Mugada)]

You can perform these steps, if the number of pending CSQ messages is zero -

1. Shutdown the Integration Server (IS has a lock, this is why you cannot delete/modify it without the IS being offline)
2. Take a backup of the LWQ_DB_MESSAGING_CSQ directory and its contents
3. Delete the directory
4. Start the Integration Server
5. [Important] If there are pending messages, then use a Derby client (such as SQuirreL) to connect and perform database compression using Derby commands

Caveat - These steps are from a recent Empower article, for 10.3, but I have personally done this a few years ago. I recommend testing this on a lower environment before doing this on Production.
Further, I strongly recommend opening an Empower ticket for an official confirmation since you are dealing with internal data storage.

KM

[DINESH_J (Dinesh Janarthanam)]

@nekkunj Btw is this test environment or your local instance . Adding to above check the below solution works for you…

LWQ_DB_MESSAGING_CSQ holds the persistent message which you want to publish to when your messaging system is down. CSQ use the derby file system to store the message for guaranteed delivery of the message before it expires

1. In order to stop further growing your LWQ_DB_MESSAGING_CSQ , disable the CSQ in the Messaging connection alias.
2. In order to clean up this CSQ I believe you have to either enable the connection to active Messaging system and restart so IS can able to pickup and start pushing the message to messaging provider or delete the Connection alias it should clean up these files.

Check and update us if this works for you ?

[Sandeep_Kumar]

Once CSQ uses a certain amount of disk space, then draining the messages as described by Dinesh will not reduce the file size even though CSQ may be down to 0 messages. Kasi has provided the way to reduce the size of dat file, but IS CSQ will go down to 0 messages.

IS 10.11 (planned release Oct 2021) will have updated CSQ library that will automatically compress the database without losing messages whenever IS restarts. Default settings are optimized for speed (do not spend very long time compressing very large database) and space savings (do not compress if we expect to reclaim less than half the space).

[rmg]

oh wow next release is going to be v10.11 (huge jump from latest v10.7) ? Very Interesting

Cheers!