Hot answers tagged

22

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.) The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water ...


9

The table MY_TBL contains large binary data in a BLOB column. The documentation of the REORG command says that DB2 avoids reorganizing such objects because it is time consuming and does not improve clustering. However, DB2 can be forced to reorganize LOB data if the LONGLOBDATA option is specified. The unused space can be reused by DB2, so inserting new data ...


8

There's no restriction on the specific name, other than it must be 8 characters or less. So BACKUP works just fine as a database name: $ db2level DB21085I This instance or install (instance name, where applicable: "db2inst1") uses "64" bits and DB2 code release "SQL10055" with level identifier "0606010E". Informational tokens are "DB2 v10.5.0.5", "...


6

Yes. Tablespaces in DB2 have a MAXSIZE attribute that you can use. You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER TABLESPACE to set them later. create database mydb automatic storage yes on C:\mydb user tablespace managed by automatic storage initialsize 20 M ...


5

Found the answer here. I needed to run the command db2 rollforward db database_name to end of backup and complete; This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored. EDIT: Found this nifty nugget while attending a DB2NightShow episode. ...


5

Firstly, the expression values current_date - 1 would only be valid if Oracle compatibility mode were in effect -- it mimics the Oracle's datetime arithmetic where the default interval is expressed in (potentially fractional) days. I think that regardless of Oracle compatibility, the range bounds should be comparable as integers, and comparing DATE values ...


4

There are several ways of doing this in Db2 for LUW. Firstly, there is a function IDENTITY_VAL_LOCAL() that returns one most recently assigned identity value; it can be used when inserting single records: insert into parent_tab (some_column) values ('foobar'); insert into child_tab (parent_id, some_other_col) values (identity_val_local(), 42); assuming ...


3

It is Java version 1.8.0. General availability date of Db2 LUW 11.1 is today by the way....


3

A table is placed in the reorg pending mode if you alter its physical structure, such as add or drop a column or change the column data type or nullability. Some operations do not place the table in reorg pending, but reorganization is required for the changes to take effect, for example, changing the table COMPRESS attribute or the inline length of a LOB ...


3

REORG is used to help DB2 point to accurate data (ie, indexes should become aware of fresh data and no longer include deleted data), as well as "collapse" empty page space created by deletion of data and/or indexes. It can also help move data that is related closer to each other for more efficient access (especially true in the case of a cluster index). ...


3

DB2 shouldn't have issues at all with storage of large files. Some of how you store them will depend on what exactly you are storing. Are you storing XML files? Large text files? Word documents or PDFs? Video? Audio? You have several options available to you. Graphic strings, binary strings, your large objects (CLOB, BLOB, and DBCLOB), and just plain XML. ...


3

You really should be reading manuals, not "pruning logs". Your database is set up for circular logging, which means there is nothing to "prune". If transactions occasionally require extra log space, increase the value of LOGSECOND. Too many primary logs will slow down the database startup, as they are preallocated. Secondary logs are allocated as needed, ...


3

I believe DB2 Recovery Expert is the best option. In the case of a dropped object, or recovery to a specific point in time, you should use the "Recovery" option. The "undo SQL" option performs Log Analysis, which only affects data, not structure. Please check IBM's official webpage : DB2 Recovery Expert


3

DB2 Express edition is (actually was, as it's not available anymore) not free; DB2 Express-C is the free edition. If you license a paid edition of DB2 its license file will be available for download from the usual source, IBM Passport Advantage. DB2 Express-C license is not available separately, it can only be had by downloading and installing the Express-C ...


3

The GRAPHIC data type seems to be a remnant from the times when there was no widespread Unicode support. It was there to facilitate storage of double-byte characters. In essence GRAPHIC(3) is equivalent to CHAR(3 CODEUNITS16). Apart from the length semantics (1 byte vs. 2 byte characters) (VAR)CHAR and (VAR)GRAPHIC are functionally equivalent. Now that you ...


3

One way is to use MERGE (best thing since sliced bread?). MERGE INTO T x USING ( SELECT 1 FROM SRCTABLE ) y (dummy) ON 1=0 -- Always false WHEN NOT MATCHED THEN INSERT (x) VALUES (DEFAULT); This will add one row to T for every row in SRCTABLE. It would be cool if we could grab the generated values with the use of transition tables, but it ...


3

The REOPT(ALWAYS) option conflicts with the explain facility behaviour, because in this case the statement compilation is delayed until the bind variable value is known -- which is the whole point of reoptimization, isn't it. The manual says in particular: The REOPT bind option defers statement compilation from PREPARE to OPEN or EXECUTE time, when the ...


3

You're thinking of Materialized Query Tables or MQTs. They are still part of the product, see this documentation.


3

The easiest way to rotate the db2diag.log file is to use the db2diag utility with the -A ("archive") option. It will rename the current db2diag.log file (appending the current timestamp to the file name). db2diag is also quite useful for searching through huge db2diag.log files (instead of trying to open them in a text editor). It has many options to ...


3

EDU is an abstraction over the underlying OS scheduling unit, a sort of a surrogate key assigned to whatever is doing actual work in the OS. If the OS has true threads, distinct from processes, such as Windows, an EDU will map to a thread. On modern Linux, which doesn't have threads but rather lightweight processes (LWPs), an EDU would map to one of such ...


2

The tablespace content type (Datatype in your second example) is defined in sqlutil.h: /* table space contents */ #define SQLB_TBS_ANY 0x0 /* All permanent data. Regular table */ /* space. */ #define SQLB_TBS_LARGE 0x20 ...


2

You cannot change the database code set after creation.


2

I came up with a solution for your data set based on some work that I was doing and some modifications to the solution posted by Serge Rielau and Rick Swagerman on IBM's developerWorks (https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/how_to_split_a_string_into_a_set_of_rows_anti_listagg12?lang=en). Data Setup queries: DECLARE GLOBAL ...


2

Assuming you have a newer version of DB2 (10.1 or higher, although I want to say a few of these are in 9.7). You have the Monitor views and functions. The MON_CURRENT_SQL view and the MON_GET_ACTIVITY table function look like they could possibly be what you are after.


2

I dont think CURRENT PACKAGESET is a special register in LUW 10.5. The special registers can be found at: https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008404.html?cp=SSEPGG_10.5.0%2F2-12-2-5 SET CURRENT PACKAGESET is a statement that can only be embedded in an application program: https://www-01.ibm.com/...


2

The communication buffer size for remote clients is determined by the RQRIOBLK database configuration parameter (more info in the manual). However, the statement you quote is overly simplistic and does not make much sense for DB2 configuration. Firstly, DB2 will perform direct disk I/O, so the OS read-ahead setting, if such thing even exists, will not have ...


2

SQL is closed so the result of a query is a new table that can be futher used. You can therefore join the result of your duplicates query with the original tables. Something like below should work: select t2.* from ( SELECT A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE FROM SCHEMA.PERSON A JOIN SCHEMA.ADDRESS B ON A.ADDR_ID = B.ID ...


2

To Quiesce means to pause/alter an application/device to allow the application/device to achieve a consistent state. In terms of DB2 the Quiesce command should force all the users off the specified database and should flush all the buffers to disk (been a while since I've used DB2). While the database is a Quiesced mode there are users who can still ...


2

The db cfg parameters that affect how backups is retained is: NUM_DB_BACKUPS REC_HIS_RETENTN You can find a description of these at for example: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.8.0/kc_gen/plugin-root-config-gen2.html I don't use datastudio myself, so perhaps there is a dedicated function in there, but I guess you can always use the ...


2

The reason for this behavior is that DB2 must have enough space for the largest possible LOB locator in the column for the row in the table. INLINE LENGTH is not like a VARCHAR where space is used only according to the amount of data. It's like CHAR – the size you specify is reserved in every single row of the table. From the documentation (scroll ...


Only top voted, non community-wiki answers of a minimum length are eligible