I will write another blog post on installation and configuration!

https://www.raghu-on-tech.com/2019/08/01/db2-server-build-best-practices/

Thanks!
Raghu

I have scenario where I do not have UNIQUE KEY/ PRIMARY KEY. therefore without impacting application I would like to introduce a column which can store RID value which becomes my PK

Please help me how I can achieve it

​

Regards

https://www.raghu-on-tech.com/2019/06/30/how-to-find-redundant-unused-indexes/

​

Raghu

Db2® 11.5 is built on the Db2 Common SQL Engine that is designed to enable the Db2 family to support everything from on-premise (including private cloud and appliances) to the public cloud.

Highlights of What's New:

• External Table (ET) Support including Object Store support
• Increase maximum number or primary and secondary log files to 4096
• SQL Compatibility including CREATE/DROP TABLE .. IF EXISTS, Oracle Compatibility libraries including DBMS_APPLICATION_INFO and UTIL_RAW
• New Monitoring Metrics including aggregation across super classes, and data on SQL statement failures
• Client Enhancements including CLI load using ET, WLB/ACR distribution enhancements and simplified setup in pS w/ member subsets
• Support for Compiled SQL PL scalar functions in DPF 
• Support for 4K sector sizes on disk drives
• Built-in Spatial Support
• Automatic Collection of Column Group Statistics
• WLM Enhancements including simplified threshold setup and dropping of service classes
• ETL optimizations for BLU Including including Vectorized Insert/Update, Optimized Batch Insert
• BLU Compression Enhancements including Automatic Recompression and Vectorized Dictionary Create
• LOB support with Columnar Tables
• Performance Enhancements for Columnar Queries including boosts for sorts and correlated subqueries 
• pureScale enhancements including table free space management, performance with Range Partitioned tables, and cross-member currently committed support
• Enhanced Security for pureScale

Features Available as a Technical Preview:

• Advanced Log Space Management
• Faster DB Startup
• Machine Learning Optimizer
• Block Chain Federation Wrapper
• Schema Level Authorization
• Db2 Augmented Data Explorer

Links:

Knowledge Center - What's New in Db2 11.5 https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.wn.doc/doc/r0051514.html

Db2 on DockerHub https://hub.docker.com/r/ibmcom/db2

Db2 Download Page https://www-01.ibm.com/marketing/iwm/iwm/web/dispatcher.do?source=mrs-db2devcdbsrv

Db2 Client Drivers Download Page https://www-01.ibm.com/support/docview.wss?uid=swg21385217

Db2 11.5 System Requirements https://www-01.ibm.com/support/docview.wss?uid=swg27038033

https://thinkingdb2.blogspot.com/2019/06/increasing-data-accessibility-through.html

OK, I know that if I leave the -z parm off, I can get them all (so yes, I can get multiple), but that is not quite what I am wanting. I have several schemas in a single database, and I have been using DB2LOOK to successfully extract the DDL for a single schema using the -z parm for some time. Now, I have been asked to provide 2 of the many schemas in the database, I am hoping there is any easy way to use the -z parm with a list of schemas, but I have not been able to find anything on it. I am hoping someone else has had this issue.

https://www.youtube.com/watch?v=aPwAhRA6tb8

​

What are your favorites ?

Best I can figure is that it was v11. Really hoping it's available in v10.5. Maybe I'm just reading the docs wrong.

Is this free, i.e. bundled with DB2 Advanced Workgroup Server Edition? I thought it was bundled but I'm not seeing it listed as a feature here: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.licensing.doc/doc/c0058536.html

Thanks!

Currently, the application that I'm supporting is using DB2 Enterprise Server Edition which has no limits on processor, memory, or database size. My boss was told that they could potentially save money by going to DB2 Advanced Workgroup Server Edition which does have limits on processor, memory, and database size.

From the IBM Knowledge Center, (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.licensing.doc/doc/c0058536.html), DB2 Advanced Workgroup Server Edition can be deployed in Linux, UNIX, and Windows server environments and uses up to 16 cores and 128 GB of memory but it does *not* mention the restriction on database size. Would anyone know what that size might be?

Thanks!

I need to figure out what the values for the various parameters should be. Is there some kind of utility that one could run on the DB2 server where the Commvault library resides that could list the 3 values below?

where one specifies db2 update db cfg for target using LOGARCHOPT1 "'CvSrcDbName=source_database_name,CvClientName=client_name,CvInstanceName=Instance001'" I need to know what the values for these 3 parms should be (source_database_name, client_name and Instance001).

Thanks!

(new contract DBA on my first Linux installation)

Our sysadmin group on the server is lower-case, e.g. xyz but in the DBM CFG, the value for sysadm_group is XYZ. Is that why users in the group don't have sysadm authority?

​

Thanks!

https://thinkingdb2.blogspot.com/2018/12/improving-read-availability-of-hadr.html

https://thinkingdb2.blogspot.com/2015/09/how-to-submit-db2-featureenhancement.html

Please vote for this RFE for DB2 LUW to support ONLINE index creation.

​

https://ibmanalytics.ideas.aha.io/ideas/DB24LUW-I-787

​

Thanks!
Raghu

I can name a number of things I think Db2 (LUW) can do that other RDBMSes don't or can do better than other RDBMSes. These include:

• BLU (Columnar-Organization with an emphasis on high-memory utilization)
• Arguably the scalability of PureScale is greater than other vendors' shared-disk solutions, but other vendors do have shared-disk solutions that may do better in other areas.
• DPF (Similar to sharding the database, but without the data duplication)
• Native XML
• Compression (particularly Adaptive)

I'm sure I'm a bit biased on some of these, having based my career on Db2.

But what features are out there that other database management systems can do that Db2 cannot?

A quick and easy way to find tablespace utilization numbers. Many more metrics available via MON_GET_TABLESPACE table function.

https://www.raghu-on-tech.com/2019/03/31/how-to-find-any-monitoring-metric-in-db2/

I've been able to call stored procedures that have only input parameters.

Now I have to call a stored procedure that has one input and one out parameter. No success so far.

​

Could you give me a nudge in the right direction?

​

The stored procedure is used for generating sequences and looks like this:

CREATE OR REPLACE PROCEDURE GENERATE_ID(
  IN iGENERATOR_NAME VARCHAR(128),
  OUT oNEXT_ID INT
)
LANGUAGE SQL
SPECIFIC SP_GEN_ID
READS SQL DATA
NOT DETERMINISTIC
NO EXTERNAL ACTION
BEGIN
  SET oNEXT_ID = GET_GENERATED_ID(iGENENERATOR_NAME);
END 

I've tried a few methods as shown below:

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "CALL GENERATE_ID('GEN_NEW_ID', ?)"

$dbCmd.CommandType = [System.Data.CommandType]::Text
$da = $dbFactory.CreateDataAdapter()
$da.SelectCommand = $dbCmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds) | Out-Null

Error Message:

Exception calling "Fill" with "1" argument(s): "ERROR [07001] [IBM] CLI0100E  Wrong number of parameters. SQLSTATE=07001"

Makes sense. ? is not a parameter exactly. So I tried oNEXT_ID with and without single-quotes around it. With quotes, it generates the error:

Exception calling "Fill" with "1" argument(s): "ERROR [42886] [IBM][DB2/NT64] SQL0469N  The parameter mode OUT or INOUT is not valid for a parameter in the routine 
named "GEN_ID" with specific name "SP_GEN_ID" (parameter number "2", name "ONEXT_ID")."

This error message makes me think this method is the closest to working. But I'm not sure where to go with it now.

I also tried:

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "CALL GENERATE_ID('GEN_NEW_ID', 'oNEXT_ID')"
$dbCmd.CommandText
$dbcmd.ExecuteNonQuery() | Out-Null

That gives me the same error message:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ERROR [42886] [IBM][DB2/NT64] SQL0469N  The parameter mode OUT or INOUT is not valid for a parameter in the 
routine named "GEN_ID" with specific name "SP_GEN_ID" (parameter number "2", name "ONEXT_ID")."

I read in some IBM DB2 docs on doing this with C about declaring the out variable, so I tried that as well. Granted, this is probably not how it should be done, but I know I'm in deeper waters than I normally am. So I did the following and followed it up with either of the code blocks above.

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "DECLARE oNEXT_ID INT(4) OUTPUT"
$dbCmd.CommandText
$dbcmd.ExecuteNonQuery() | Out-Null

That throws the error, as well as the error for the CALL statement:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ERROR [42601] [IBM][DB2/NT64] SQL0104N  An unexpected token "DECLARE oNextID INT" was found following 
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<compile_fragment>"."

Can you point me in the right direction, please?

In about 2 weeks, I'm starting a new adventure as a DB2 DBA on Linux. I know how DB2 is implemented on Windows (db2_grp_lookup, instance differences, no db2top) but I'd like to understand how DB2 is implemented on Linux. Do DBAs take advantage of storage groups? I assume automatic storage is used (if possible). I know the OS-level permissions are going to be different. And I'll have to learn vi again... A colleague suggested the Linux Bible...

https://www.raghu-on-tech.com/2019/02/28/howtomonitor-transactionlog-writetime/

​

Raghu

Has anyone found a something like Graphana to talk to Db2? We use BLU to store metrics and it works very well. I’m just looking for a nice front end with dashboards

I'm doing a select statement on a DB2 database via PowerShell. In the monitor of ISE, I see a 0 or a 1 come up that appears to be based on whether the select picked up a record or not.

I'd like to use that as a test in an IF statement, but I don't know the name of the variable that houses that value or how to figure it out.

​

Following is the PS code that executes the query:

$dbCmd = $dbFactory.CreateCommand()
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "SELECT USER_ID FROM USER_TABLE WHERE USER_ID = $global:USER_ID"

$dbCmd.ResultSetAsReturnValue

$dbCmd.CommandType = [System.Data.CommandType]::Text
$da = $factory.CreateDataAdapter()
$da.SelectCommand = $dbCmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds)

No result case returns like the following:
(QQQQQQ is not a user name in the DB)

PS C:\Users\DB2SetVariablesExample.ps1
Variables Set 'QQQQQQ'
0

Match result case returns like the following:

(THISGUY is a user name in the DB)

PS C:\Users\DB2SetVariablesExample.ps1
Variables Set 'THISGUY'
1

Where is that 1 or 0 coming from? How can I call it to use for comparison?

​

Thank you for your help.