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", "...


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 ...


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

As mentioned in comments on the question, MERGE is the answer you are looking for. Check out this great blog as well: MERGE statement explained by Serge Rielau (瑞赛奇) and Rick Swagerman. It will look somewhat like this for your case: MERGE INTO <tab> AS ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT...


2

One possible cause might be that someone created a sample database using db2sampl. The sample database contains both of these artefacts: ]$ db2 -x "select tabschema, tabname, type, clustered from syscat.tables where type = 'S' or clustered = 'Y'" DB2INST1 ADEFUSR S - DB2INST1 EMPMDC T Y It appears as if ...


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 backup image contains only used extents, so it might be substantially smaller than the sum of tablespace sizes, depending on the tablespaces' types and their physical properties. On the other hand, in DB2 10.5 the backup image will by default include log files that are modified during the backup, so depending on how active the database is during the ...


2

After opening a support case with IBM, they gave me the clue that lead to a solution: -> Could you first right click in the title bar of the Command Window and take "Properties". Is it using a TrueType font for displaying characters? As it turns out, changing the font (wtf?) changes the display of said characters. This is some specific behaviour ...


2

As you mentioned cron, I'm assuming your Db2-Connect runs on some flavour of Unix/Linux. For those environments scripting in ksh or bash is ideal for Db2 products because they are very script friendly. Check also if such work would be redundant, as some sites have host based (z/os or i-series) methods for monitoring activity from remote Db2-connect servers. ...


1

Theoretically - yes, it’s possible. You may relatively easy create a couple of external UDFs (there are standard java classes for this already) for compression / decompression (CLOB<>BLOB). Decompression function may be used in a view on the base table. Compression function - in the instead of insert/update triggers on this view. Your applications may ...


1

Historically LOB data are stored in data structures separate from the normal row data, these data structures are not compressed. With Db2 for LUW 9.7 and later you have an option to store part of LOB columns, subject to the row length limitation, inline with data rows. Inline LOB data will be compressed along with the rows they belong to. CREATE TABLE foo (...


1

No. Db2 for i (aka AS/400) dates are stored internally as a Scaliger number (aka Julian Day number) The number of days since noon on Monday, January 1, 4713 BC. select hex(current_date) from sysibm.sysdummy1 returns 002583EC as of about 3pm MDT April 30,2019 The decimal value is 2,458,604 Which agrees which the current value on the web page linked to ...


1

The -601 happens because table WCSSTGAP.XI_CATENTRY_PRICE_0 already exists , so it cannot be created again while it exists. Either drop that table manually and retry, or check back in the log file(s) to find if a previous DROP TABLE WCSSTGAP.XI_CATENTRY_PRICE_0 did fail (and then solve that problem first). You will need to find root cause.


1

Looks like, when the alternate server is configured, the CLI driver prefers its proper hostname over "localhost". If you specify the machine hostname or IP address in the connection string instead of "localhost" the script works as expected. The test script can be modified as: [...] import traceback import socket [...] def f1(dbname, username, pwd): ...


1

Apparently, this was the switch module. The RM OPEN STRING definition included: rmopenstring=db=mydatabase,uid=uuuuuu,pwd=pppp,,axlib=somelib Once the value for axlib was supplemented with an extention, .so, the error has gone away.


1

Rough idea on how to get database objects in the correct order. The dependency graph is not complete but seems to fulfil my needs. #!/usr/bin/python3 import ibm_db import ibm_db_dbi from toposort import toposort, toposort_flatten cfg = ... conn = ibm_db.connect("DATABASE=%s;HOSTNAME=%s;PORT=50000;PROTOCOL=TCPIP;UID=%s; PWD=%s" % cfg,"","") find_edges = ""...


1

Few years ago we migrated our DB2 from 9.7 to 10.5. We also moved the database to new hardware and implemented data compression. Because of this we decided to create the database from scratch and export and import the data. We used db2look and db2move to do the job. However, there was no way to get the DDL created by db2look in the correct order. We had to ...


1

Several years ago I did put together a package of db2utils which included a procedure called DROP_SCHEMA. This was written before ADMIN_DROP_SCHEMA became available in DB2, and uses a fairly crude mechanism for determining what to drop, and in what order. To whit: it uses a large UNION query to get all objects belonging to the schema from the various SYSCAT ...


1

This can happen as Formula 4 is about the cluster ratio. This is the correlation of the index sequence to the sequence of the data. The documentation also states (same page) The clustering ratio of an index should be greater than 80 percent. When multiple indexes are defined on one table, some of these indexes have a low cluster ratio. (The index ...


1

The only problem seems to be your missing quotes for the dates. As they are enclosed in the string the have to be escaped (with a second single quote) like in my example: CALL SYSPROC.ADMIN_MOVE_TABLE( 'ADMIN', 'TRANSACTION', '', '', '', '', '', '(TRANSACTIONDATE) (STARTING FROM (''1/1/2010'') ENDING AT (''1/1/2017'') EVERY 1 MONTH)', ...


1

Problem solved by setting Security options/User Account Control: run all administrators in admin approval mode = Disabled in Local Security Policy


1

You can achieve something like this using a federated view. After configuring the federated data source as described in the manual, you would create a nickname for the archive table CREATE NICKNAME Archive_Prod FOR archive_server.archive_schema.Archive_Prod; After that you would create a view joining the two tables: CREATE VIEW v_active_prod AS SELECT ...


1

Readers should not block readers, so if no one writes to the table I don't think using UR instead of CS will make much of a a difference. There is a small overhead I guess for applying and releasing the share lock, but my guess is that it will be negligible. I guess only way to find out for sure is to try and see if it affects throughput. I was unaware ...


1

You cannot alter the LOGGED attribute of a LOB column. However, you can alter the table by adding a new LOGGED LOB column, update the new column from the existing column, then drop the original column. It may require less wasted disk space but is unlikely to be faster than using ALTOBJ.


1

FWIW, I tried investigating the differences between a newly created index in a db copy and the original index in syscat.indexes, sysibm.sysindexes and sysstat.indexes. The only difference I could spot was a slightly better density (100) for the new index compared with the old one (87). I therefore ended up recreating the index, and now the execution plan is ...


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