Questions tagged [db2-luw]

DB2 for Linux, UNIX, and Windows.

Filter by
Sorted by
Tagged with
0
votes
1answer
21 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
24 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
21 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
36 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
22 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
29 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
33 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>...
0
votes
0answers
17 views

Find multiple substrings from a column entry in DB2

I have a Table like this | ID | otherID | Data +--------+-----------+---------------- | 1234 | 56789 | This is a Name: Charlie; string <name_starts> with Name: Alpha; also some more ...
2
votes
2answers
102 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
32 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
42 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
0answers
13 views

how to receive notification in java?

I am working on a project where i have to made a user to ask questions with subject experts,when user select one subject (like java) then it searched in subject column of expert table, if the subject ...
0
votes
1answer
75 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
46 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
115 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
36 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
40 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
49 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
71 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
21 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
111 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
66 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
26 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
40 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
35 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
615 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
23 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
31 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 ...
0
votes
2answers
211 views

How to change default db2 database admin username to something else in Linux?

I have a DB2 database version 11.1 on Linux - rhel 7.7 in my test environment. I need help on how to change the default db2 admin username to something else, ie. db2inst1 to dbroot. And the new db2 ...
0
votes
1answer
855 views

Connection Error while connecting SQL Developer with DB2

I am trying to connect Oracle SQL Developer with DB2 Database. I have added db2jcc4.jar, db2jcc.jar and db2jcc_license_cu.jar Libraries in "Third Party JDBC Driver" DB2 tabs appears Entered Username ...
1
vote
2answers
73 views

Differences between EDU and thread

In DB2, there is this command that shows active EDUs (engine dispatchable units): db2pd -edus There are two fields in the respective output: EDU ID and TID. According to this db2pd page from the ...
0
votes
1answer
75 views

adding 2nd catalog for linked server to DB2

I am unable to figure out how to add multiple catalogs for a DB2 linked server. I have an initial Linked server setup and working properly, but I am struggling to figure out how to add multiple ...
1
vote
2answers
159 views

DB2 Load Utility Primary Key vs. Unique Index

In short, does the Primary Key double as a Unique Index? Some background: I am trying to load a bunch of data into two tables using the DB2 Load utility loading in IXF file types. The data is ...
2
votes
2answers
324 views

Db2 REORGCHK returns the SQL2310N (Error “-668”) message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
0
votes
1answer
35 views

DB2 Audit Policy not producing EXECUTE data

I'm trying to set up an Audit facility on a DB2 AWSE V10.5 FP5 database running on Red Hat Linux Server 6.8. The definition for the policy is : ALTER AUDIT POLICY Failures CATEGORIES ...
0
votes
1answer
87 views

Insert values with leading zeros

How do I insert values with leading zeros in it? Example. db2 "insert into db2inst1.asd_currency (currency_code, currency_name, currency_decimal_count, currency_code_desc) values ('007', 'afghani'...
2
votes
3answers
408 views

In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers. Question: Is there a time where it would be correct to use 4K rather than 32K? If so, why? (...
0
votes
1answer
82 views

Error trying a manual's example in DB2 11.1.4.4

When trying this example from the DB2 11.1 manual: VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER)); I get this error: SQL0171N The statement was not processed because the ...
0
votes
2answers
274 views

Is it possible to compress CLOB columns in DB2 10.5

I have a table with a CLOB(1M). If I enable compression (compression yes adaptive) on the the table the CLOB is not compressed. Is there a way to make this work in DB2 10.5?
1
vote
1answer
71 views

db2 cross platform function/variable?

Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not ...
0
votes
1answer
114 views

How to stop a db2 instance properly?

I have a db2 instance, and I need to stop it properly, clean, without any db2stop force. I need this in order to restart it fast in a batch process. I am completly sure there are not connections or ...
0
votes
0answers
46 views

drop_cache effects on database

I have several machines with: databse: db2 10.5 OS: CentOS 6.9 on some of machines cached memory is high and i need to run this command to free some memory: echo 3 > /proc/sys/vm/drop_caches ...
0
votes
1answer
57 views

DB2 LUW V10.5 on Windows. How to Manually rotate the Diaglog using scripts

In DB2 LUW V10.5 on Windows, is there a way to manually rotate the diaglog log file? Currently, it is almost 1GB and it is difficult to open it to find info.The diagsize is set to 0 and I cannot ...
0
votes
1answer
2k views

DB2 - how to call a stored procedure that returns a result set in another user defined table function

I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor. Now I want to write a table function in db2, that will call ...
0
votes
1answer
244 views

Migrated data with Special Characters Counted as 2 Characters in DB2 Linux

We are currently migrating data from DB2 10.1 AIX to DB2 11.1 Linux. Since we are using some features like versioning/history tables and rowchangetimestamps, db2move is not an option for some tables. ...
1
vote
1answer
69 views

determine page size for index

I'm in the process of migrating a set of databases from code set ISO8859-1 to UTF-8, STRING_UNITS=CODEUNITS32. I suspect that quite a few indexes will no longer fit in there current tablespace page ...
-1
votes
1answer
117 views

DB2 reporting incorrect filesystem full error? [closed]

DB2 V10.5, RHEL 7.5 We had an issue when DB2 was stuck not being able to write log files to disk because of disk full error just that there was more than enough free disk space on the filesystem ...
0
votes
2answers
172 views

Monitor delete statements on a specific table on DB2 LUW

Background User is claiming that records are deleted from a specific table, but nobody knows why. They are not sure if it's an application error or done by a user. Question Is it possible to ...
1
vote
2answers
41 views

My application uses set schema and unqualified tables and I get confusing numbers from snapdyn_sql

More of a finding, than a question, I'll add an answer in a minute. The other day I stumbled upon an application that used several tables with the same name, but in different schemas. Example: ...
0
votes
1answer
234 views

How to Restrict INSERT operations on table for DB2?

I have a table OAUTH20_TOKEN_CACHE for which I have restricted INSERT operations by running below command. db2 REVOKE INSERT ON HVD121.OAUTH20_TOKEN_CACHE FROM USER HVD121 This could be seen ...