Questions tagged [db2-luw]

For questions about IBM Db2 for Linux, UNIX, and Windows. Please also add the generic [db2] tag.

Filter by
Sorted by
Tagged with
0
votes
0answers
4 views

Error trying a JSON_VALUE example in DB2 11.1.4.4

I'am having a similar problem as described in this question. Error trying a manual's example in DB2 11.1.4.4 DB2 Version: DB2 LUW 11.1.4.4 When running the following sample from the DB2 LUW 11.1 ...
0
votes
1answer
12 views

How to identify row change timestamp columns in Db2 Table

I need to copy some Db2 tables programmatically (java 11 using the standard Db2 jdbc driver). The tables in question lie on a Db2 for z/OS database (v12) and on a Db2 LUW database (v11.1). The target ...
0
votes
1answer
20 views

Granting select on syscat.schemata - DB2

I was wondering if it is safe to grant select rights to syscat.schemata to users on a DB2 database? What could go "wrong"? Some tool like DataGrip and DBeaver cannot load connection schemas ...
0
votes
0answers
23 views

Meaning of sqlstate in activity monitor table?

I'm investigating phenomena for a transaction and created an activity monitor. db2 "create event monitor act_stmt for activities write to table manualstart" db2 "set event monitor ...
0
votes
0answers
37 views

How to restrict CPU consumption of CPU intensive applicaiton with Db2 Workload Manager?

in Db2 v11.1 on Linux I would like to have control over applications that consumes too much CPU resources. For every application that consumes too much CPU for more then 3 seconds I want to lower CPU ...
0
votes
0answers
25 views

Boost STMM in Db2 by mimicking values of previous execution

Let’s say I have two kinds of workloads / processes that execute at different times (i.e. daily workload, nightly workload). And, the STMM is activated. The database is not alone in the instance, and ...
0
votes
2answers
85 views

How to move database from Db2/Linux/System-z to Db2/Linux/Intel

our company has decided to migrate database: from source Db2 Enterprise Server Edition v11.1 fixpack 5 on Linux/System-z (this is still Linux/Unix/Windows type of database despite running on System-z ...
-3
votes
1answer
32 views

DB2 replace emoji in text string

I trying to find a way to replace emojis within a text string, I want to be able to set a pattern of (a-z) and (0-9). if the string has other characters that don't match that pattern, i want to ...
3
votes
2answers
99 views

Display line number where error occurred in DB2 stored procedure

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions ...
0
votes
1answer
47 views

local administrator does not have permissions to create database in db2

I have a fresh standard installation of DB2 11.5 on Windows server 2016. According to documentation, i need the to have SYSADM or SYSCTRL to create a new Database. The documentation for "...
1
vote
1answer
36 views

DB2 equivalent of Oracle DBMS_LOB.SUBSTR etc

I have to migrate an Oracle stored procedure to DB2 LUW 10.5. It uses DBMS_LOB.SUBSTR() function on a BLOB parameter that receives an array of bytes from the client code. What is its equivalent in DB2?...
0
votes
0answers
41 views

Confused by boolean

After reading Casting between data types I wanted to create a table with a column that could store boolean values: create table t(a smallint default 0) insert into t(a) values (default) select cast(a ...
2
votes
2answers
91 views

Get current table name trigger was fired on

My system: Db2 11.1 on Linux. 1.Create some table: create table admin.tab (a int not null primary key, b int) @ 2.Create audit table: create table admin.audit (table_name varchar(2000), datetime ...
0
votes
0answers
25 views

How to add generated timestamp on existing table

on Db2 v11.1 fixpack 5 on Linux I have table with automatically generated timestamp: create table admin.tab ( id int, datetime timestamp not null generated always for each row on update as row change ...
0
votes
0answers
32 views

What C# type is the correct representation for DB2 UDB boolean?

The project that I work on uses IBM.Data.DB2 nuget package for its data layer. I need to call into utl_mail.send_attach_varchar2 stored procedure on the DB2 side. It has a boolean parameter att_inline ...
0
votes
0answers
19 views

Not able to create a stored procedure for a table insert in DB2 UDB 10.5 [duplicate]

I want to create a very simple stored procedure that will only take several IN parameters and insert them into a table. To learn how to do it, I went to CREATE PROCEDURE (SQL) statement From that I ...
0
votes
1answer
68 views

How can I add a user and specify a password for a in IBM Data Studio? [duplicate]

In Data Studio I can connect to a sever/database, go down to Users And Groups, right-click on Users and add a new one. But the program never asks me for a password. How can I specify it? Also, no ...
0
votes
1answer
30 views

How can I list all the tablespaces without getting into each instance?

I got around 98 instances in a single AIX server. I need to list all the tablespaces, their path and usage. Is there a way I can do that without getting into each instance, connecting into the ...
0
votes
1answer
34 views

Benefits of reorging a new table with loaded data?

Given the following scenario: DB1: EXPORT TO T.IXF OF IXF ... modified by codepage=... SELECT * FROM T; DB2: CREATE TABLE T (...) COMPRESS YES ADAPTIVE ORGANIZE BY ROW; CREATE INDEX ... ON T (...) ...
1
vote
1answer
65 views

DB2 BOOLEAN type, EXISTS predicate and nullability strange behavior

I'm using DB2 LUW v11.1.4.5, It seems that EXISTS predicate, when FALSE returns NULL (is it documented ?) In the following query on X is null is true, all the rest acts like FALSE is a synonym for ...
0
votes
1answer
191 views

How to set or grant SELECTINAUTH for a schema in DB2 11.5

I'm running DB2 11.5 and want to grant select, insert, update or delete rights for all tables in a specific schema. SELECTINAUTH etc. seems to be perfect for this. When I'm looking to the SYSCAT....
0
votes
1answer
55 views

last_value ignore null in left join with additional dimension?

This is just a silly example that I started to think about, but can't seem to come up with an elegant solution for. Assuming a Calendar table (I'll use Db2 syntax): create table calendar ( dt date not ...
0
votes
0answers
38 views

Change in plan after moving from ibm1252 to utf8

DB2 v11.5.0.0, RHEL7. I spent the last couple of days trying to figure out why a particular query runs so much slower on a UTF8 database, compared with an IBM-1252 database. The UTF8 db is generated ...
0
votes
1answer
28 views

Not able to change the AUTO_DEL_REC_OBJ value to ON

I changed the value of AUTO_DEL_REC_OBJ to on as below in UAT and prod environment; in UAT it's done but in prod its current value is still showing OFF. What did I do wrong? In UAT: db2 update db cfg ...
0
votes
1answer
29 views

date variable manipulation

I'm working on DB2 stored procedure, which will be called by the Korn Shell (.ksh). My DB2 version is 11.5 and it is running on AIX. I was stuck in TIMESTAMP to DATE conversion. Below is my ...
1
vote
1answer
101 views

grant procedure

A batch process run by user U drops and creates a work table T. Simplified setup: rights for user U db2 grant dataaccess on database to user U db2 grant all on table T to user U db2 transfer ...
0
votes
2answers
36 views

The leading zeroes before decimal point is missing in string formatting of decimal data in XML in db2 11.1

When generating XML that includes decimal values in Db2 11.1, the leading zero right before the decimal point isremoved. In the following query, the decimal value .00000000000000 is published even ...
0
votes
1answer
58 views

clp appends bytes to output

I am migrating a number of databases to UTF-8, and just discovered phenomena that I was not aware of. When selecting data out to the terminal, additional bytes are added to the output. Example: ~]$ x=...
0
votes
1answer
106 views

For SSL renewal procedure in Db2 LUW , DO i need to create CSR request for each instance residing in same server?

I am trying to renew my SSL certs . Stuck here if we need to have create a Certificate Signing Request for each instance residing on the same server and send it to CA who will provide us the ...
1
vote
1answer
114 views

how to alter sequence in db2 stored procedure by another user

I am using DB2 LUW Version 10.5.0.5 on Linux (x86_64) I have written a procedure to alter a sequence to restart with a certain number. The procedure takes the sequence name and the number to restart ...
0
votes
1answer
44 views

does xml element exist, regardless of value?

EDIT: I demonstrated my ignorance by confusing attribute with element, I updated my question. Sorry. To clarify, I'm looking for the existence of the element code in: <conditionmessage:code>...
3
votes
2answers
360 views

New bufferpool not being used

As part of a migration, I created a new bufferpool - say BP8K - size 8K, and a tablespace using that bp, and a table in that tablespace, that is loaded with data. However, I get a: SQL1218N There ...
0
votes
1answer
42 views

Db2 LUW tens of millions of rows

We have a table with tens of millions of time series rows. We index by three foreign keys to relations. Let's hypothetically say medical observation row to doctor, acl, and person. Main thing being a ...
-2
votes
1answer
138 views

How to resolve error “SQL0104N” when running “db2 prune logfile prior to” command?

I wrote an script to delete transaction log files in db2. I need to delete all the files prior to the active log file.I wrote this code as .sh file db2 connect to DWHDB db2 get db cfg | grep '/...
0
votes
1answer
107 views

Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables? [closed]

These are my tables: CREATE TABLE uregister( u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR(150) NOT NULL, u_mail VARCHAR(150) NOT NULL, u_password VARCHAR(8) NOT NULL, PRIMARY KEY(...
0
votes
2answers
89 views

Overview of leaked/unclosed cursors/statements on DB2 LUW server?

We're encountering a "too many open cursors issue" on DB2/LUW. Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors? We're doing ...
0
votes
0answers
512 views

Cannot connect to DB2 from command prompt on TLSv1.2 enabled port

I am trying to connect to DB2 version 11.x on linux from my local windows machine. Ran below command in by ibm's jre/bin, but still getting an error. Command C:\Program Files (x86)**>java com.ibm....
1
vote
2answers
79 views

Adaptive compression and HADR

I'm looking into adaptive compression. I can't seem to find any information on how HADR is affected when a table is compressed and then reorg:ed. Example t=mytable db2 "alter table $t compress yes ...
0
votes
1answer
42 views

Improving performance of mapping standardized values with non equi joins - LIKE operator

I have a large-ish data warehouse staging table (~2.5 million rows) that is using a mapping table to standardize a text column. I have an update statement that works perfectly, but has terrible ...
1
vote
1answer
60 views

Distribution statistics on VARCHAR / VARGRAPHIC columns?

It appears that only the first n characters is used as colvalue for varchar/vargraphic columns. From my test below it appears to be 33/16. Is the length documented somewhere? I tried to search the ...
0
votes
2answers
205 views

Can indexes be deactivated for particular queries in DB2?

I'm trying to evaluate the impact of a new index on various queries that run against a DB2 11.1 database. It would be convenient to be able to force the optimizer to ignore the new index on a query-...
0
votes
1answer
40 views

Difference between DataLinks and External tables

There was a feature called DataLinks that allowed to access data in files from SQL. This was introduced in Db2 V5.2 and removed in V9.7. However, in v11.5 there is a "new" feature called external ...
0
votes
2answers
205 views

Archive old data in a large table in DB2

I have a very large non-partitioned table with ~4,500,000,000 records: ODS.LARGE_TABLE. The table has stored 3 months of data with ~50,000,000 rows per day, therefore resulting total of ~4,500,000,...
0
votes
1answer
159 views

DB2: how to export data file and own by db2inst1 via ADMIN_CMD

There is some requirement that I need to run the export command in Java via JDBC. Hence below is the command I execute CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/header.csv OF DEL SELECT * FROM MYTABLE');...
1
vote
0answers
69 views

Support for JDBC 4.2?

As far as I can tell, the jcc driver shipped with 11.5 (https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads) still uses JDBC 4.1. Any rumours of, if a later fixpak will switch to ...
0
votes
2answers
52 views

Errors in DB2 8.0 db2diag.log

We have a very old PeopleSoft HR system running on DB2 8.x. Numerous times a day we get the following errors in the db2diag.log file. 2019-11-04-01.22.24.012784-300 E16054C794 LEVEL: Error (...
0
votes
1answer
54 views

How to ensure database is cataloged

I am planing to upgrade my database. I am referring to this website: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.qb.upgrade.doc/doc/t0007193.html On the Before you begin ...
0
votes
1answer
2k views

db2 expired password but cannot reset

Has anybody encountered this problem with a db2 server? I'm running a development database and connecting to the database gives me a password expired error. Trying to reset it will give me an invalid ...
0
votes
1answer
27 views

Switching context between two instances from one account

I have a server with two instances running on it (a dev environment) where we use one login to access either instance. I wanted to be able to switch context from one instance to the other without ...
0
votes
1answer
103 views

What's a way/ways to return Identity element (that is auto-generated) in a table from a stored procedure?

I have tables in my db with auto generated primary keys that I want to get a hold of when inserting new rows from my Java calling code. I'm trying to create stored procedures that handle the insertion ...