Hot answers tagged

3

It's a very open-ended question, as there is no sure-fire method to determine the server version and platform. You'll need to apply some heuristics. In Db2 for LUW (and only LUW) since v 10.1 there's a system view dbmcfg that can return the server version: select value from sysibmadm.dbmcfg where name = 'release' so if it succeeds you'll be sure it's Db2 ...


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


3

I think you want multiple aggregations: First find the max for each ID-Source combination, then sum for every ID: select ID, sum(MaxNumImages) as SumMaxNumImages from ( select ID, max(NumImages) as MaxNumImages from table where Source in ('A','B') and Event = '190' and NumImages is not null group by ID, Source ) as agg group ...


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

It is "partially documented"/"not really documented" AFAIK. More specifically, there is APAR: IT13369: SUB-OPTIMAL QUERY PERFORMANCE WHEN DISTRIBUTION STATS COLLECTED ON STRING COLUMN WITH A COMMON PREFIX LARGER THAN 32 BYTES that mentions it. I assume what you actually care about are cardinality estimates for a query that has predicate on a column that ...


3

Db2 does not support the INTERVAL keyword on OLAP window-aggregation-group-clause https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html so you need to convert the TIMESTAMPs to MINIUTEs first. E.g ID , "TIME" , "P/L" , SUM("P/L") OVER (PARTITION BY ID ORDER BY (DAYS("TIME") * ...


2

Yes, you can, but often times it's not trivial. Microsoft makes a free tool called SQL Server Migration Assistant (SSMA) for DB2, which provides you a great starting point. Again, migrations of this nature are not going to be just a click of a button, so you will need to thoroughly read through the documentation and do some trial runs before you decide/...


2

It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors that one might want to take into consideration when deciding on the tablespace page sizes. TL; DR. Choose one page size that is most suitable for your data, ...


2

Such a choice generally depends on type of workload in your system. Briefly: Transactional systems typically read data by few records per request. The smallest chunk of storage in Db2 is data page. To read a single data row Db2 has to read whole data page from disk. Rows needed by typical request often reside in data pages randomly, so, it’s not unusual, ...


2

I finally did hit just the right google search and found a note on a post from Ember Crooks (https://datageek.blog/2013/07/09/db2-luw-what-is-a-page/) that says smaller pages are preferred for OLTP or e-commerce sites so you're handling smaller amounts of data. I suppose that's the answer, then. Use the smallest that will work for your data, recognizing that ...


2

As is suggested in the comments, you should change the data type to, for example, char(3). If that is not possible, you can transform the value of the column like: SELECT SUBSTR(DIGITS(CURRENCY_CODE),1,3) AS CURRENCY_CODE FROM ... or perhaps: SELECT DIGITS(CAST(CURRENCY_CODE AS DECIMAL(3,0))) AS CURRENCY_CODE FROM ... which would be the same as: SELECT ...


2

Try this: with subscription_types (type) as ( values 'type1' , 'type2' ) , subscriptions (user, sub_type, subscribed) as ( values ('U1', 'type1', 'Y') , ('U1', 'type2', 'Y') , ('U2', 'type1', 'Y') ) select u.user, t.type from ( select distinct s.user from subscriptions s ) u, subscription_types t where not exists (select 1 from subscriptions ...


2

It seems to me that you want the cartesian product between users and subscription_types, minus the subscriptions: select distinct s.user, st.subscription_types from subscriptions s cross join subscription_types st except select user, sub_type from subscriptions This can, of course, be expressed in other ways, but this is probably the most obvious one.


2

As a side note, REORGCHK is not the right way to determine if the table needs reorganization post-ALTER. It is intended to analyze object statistics to determine if the table or its indexes can be optimized in some way. Subsequently, an attempt to scan its indexes to update statistics will necessarily fail. REORGCHK calculates several metrics, such as the ...


2

I'm not sure REORGCHK UPDATE STATISTICS is the way to go after a destructive operation (drop column) against a table. I suspect that the drop statement put a Z-lock on some catalog tables which prevents STATISTICS from being updated. If you are only interested in whether you need to reorg a table due to the drop you can: SELECT NUM_REORG_REC_ALTERS, ...


2

In Db2 (for LUW) V11.1 and above, you can use REGEXP_EXTRACT https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061492.html E.g. SELECT REGEXP_EXTRACT(c,'@(.*)$',1,1,'',1) FROM TABLE( VALUES ('johndoe@gmail.com') ,('jhefid@yahoo.com') ,('ipw@gmx.com') ) AS t(c) returns 1 ------...


2

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0021804.html User authentication is completed by a security facility outside the Db2 database system, through an authentication security plug-in module. A default authentication security plug-in module that relies on operating-system-based authentication is ...


2

There is no equivalent Access Request with Db2 Optimization Guidelines unfortunately. You may specify either desired index to use or another access method (TBSCAN, for example) for your table. But not "not to use some particular index" guideline.


2

You should handle possible SQLCODE = 20189 (SQLSTATE = '01657') message after CREATE BUFFERPOOL. The corresponding message looks like below: db2 "create bufferpool BP8K SIZE XXXXXX" SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory. SQLSTATE=01657 The corresponding db2diag....


2

db2stop/db2start is not required to make a newly created bufferpool useable, but in most cases you must deactivate and reactivate the database for the tablespaces to be able to use the new bufferpool. This is because, even if the IMMEDIATE option is specified or implicitly assumed If there is not enough reserved space in the database shared memory to ...


1

An error like this might be returned if you will try to connect to a port which is actually not the port DB2 server listens on. E.g. if I would, by mistake, try to connect to port 22 (ssh) I will get the same error: $ java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password passw0rd [jcc][10521][13706]Command : java com.ibm....


1

You may start DB2 table journaling with the QSYS2.QCMDEXC procedure and a corresponding text of the STRJRNPF CL command as an argument. Something like this: CALL QSYS2.QCMDEXC('STRJRNPF FILE(MYFILELIB/MYFILENAME) JRN(MYLIB/USRJRN) IMAGES(*BOTH) OMTJRNE(*OPNCLO)');


1

An EDU is a thread. In db2 9.1 db2 changed from a process model to a thread model. EDU's can be an agent, prefetcher, page cleaner, log writer, .... and they each run as a thread.


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

It's LOCATE (in DB2) which is equivalent to CHARINDEX in SQL Server. Here is the complete description of it with example. Thanks!


1

Try SELECT * FROM ( SELECT * , MAX(LENGTH(EMAIL)) OVER() AS MAX_LEN FROM EMAIL_TABLE ) WHERE LENGTH(EMAIL) = MAX_LENGTH


1

For i-series, at least for 7.1, you can try the RESTRICT clause of DROP COLUMN. There is also a RESTRICT clause for DROP TABLE. Per documentation RESTRICT Specifies that the column cannot be dropped if any views, indexes, triggers, or constraints are dependent on the column. 1 If all the columns referenced in a constraint are dropped in the ...


1

The problem is that the database must be encoded in UTF-8. https://developer.ibm.com/articles/sql-json-publishing-functions/ JSON data consists of a sequence of Unicode characters that are arranged in a hierarchical form based on the JSON value syntax and grammar


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


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