Questions tagged [db2]

The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix (namely AIX), Linux, IBM i (formerly OS/400), z/OS and Windows servers.

Filter by
Sorted by
Tagged with
0
votes
0answers
8 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 ...
-1
votes
0answers
5 views

How to schedule db2 procedures in db2 warehouse on cloud?

I'm using db2 warehouse on cloud & I have more than 100 procs to schedule houly, once in 15 min ..etc. Currently I'm using db2 ATS for that. But db2 ATS will not work if number of jobs exceeds 60 &...
1
vote
2answers
51 views

select rows on distinct column value without vendor-specific sql syntax

I'm trying to select distinct rows without any vendor specific syntax. My table layout is as follows: +--------------------------------------+---------+--------------------------+----------------------...
0
votes
0answers
9 views

Not able to find odbc driver for db2 10.5

There seems to be a problem like the DBA has lost the ODBC manager folder in the OS, either during migrating DB2 to 10.5 or deploying the application new code on last Saturday, now they can not see ...
-1
votes
1answer
19 views

DB2 - Select rows with oldest date per id and aggregate a field with similar date

Here's what I want to do (Please see photo for reference): I wanted to select from Source Table all customer (cust_id) and their corresponding oldest date of payment (pd_date) and aggregate using sum()...
-2
votes
0answers
29 views

IBM DB2 LUW - How to Grant Account Privileges to CREATE & DROP specific object types

As part of a system hardening exercise I am looking to restrict DB2-LUW privileges by individual service account and use-case. In this particular instance, i'm looking for a way to restrict the CREATE ...
0
votes
0answers
30 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
14 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
0answers
29 views

DB2 Read - Write locks [closed]

I am working on a web application which involves inventory management. My application uses DB2 as the database. In my application, there is a module which just inserts the records. This can happen at ...
0
votes
0answers
17 views

SSL Encryption Renew

When we request CSR for SSL encryption renew , can we use same label or different label could be used ? After importing the signed certificate into Db2 server ,What steps we need to follow on db2 ...
0
votes
2answers
36 views

Select Into a temp table in DB2

Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2. The root query looks roughly like this: Select Col1, Col2, Col3 ...
0
votes
1answer
22 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
26 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
23 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
46 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
37 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
22 views

Find what values are not in multiple columns [duplicate]

I need to find what "Codes" from column "RMS_CODE" and column "RMS_ALT1_Cde" are not being used in the Child # (starting in position 6 for a length of 4) column within the "APC_Spec" number. I tried ...
-1
votes
1answer
21 views

comparing one value in a column to all the values in another column at a time SQL/DB2

I have three columns as shown below. What is the best way to pull all the values from column 3 that are not the same in the other columns? the result should return 2, 7 and 9 as they are only in ...
0
votes
1answer
21 views

How to remotely run commands for a DB2 instance, preferably using ibm_db?

My objective is to see the size of Audit Buffer size, normally I would use below command on my Db2 instance db2 get database manager configuration | grep AUDIT_BUF_SZ But now I need to do it ...
1
vote
1answer
39 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 ...
1
vote
1answer
32 views

Get tablespace of a given table in DB2

What is the command to get the name of tablespace of a given table in DB2 database ?
0
votes
1answer
35 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
3answers
30 views

Convert two integers into timestamp

How do I convert two integers, one for date and one for time, into a timestamp? Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM. Given 20200304 and 946, return a timestamp for ...
0
votes
0answers
20 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
121 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
34 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
48 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
30 views

DB2 - Is there a way to get the list of tables a particular transaction has affected?

We have a scenario where we would like to see if we could get hold of a list of tables affected by a given transaction. We use attunity to listen on the transaction log, but what I get from attunity ...
-1
votes
1answer
120 views

Lock differences between Insert Into vs Select Into while using Openquery

Using SQL Server 2014. My DBA said I should be using Insert Into instead of Select Into when using Openquery because Select Into will create a lock on the linked server table. Is this true? linked ...
0
votes
1answer
76 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
53 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
1answer
28 views

Is there a way to get the contnents of an RPG program using SQL?

So, I've got myself a query to list our RPG programs: SELECT OBJNAME FROM (SELECT OBJNAME AS SCHEMA FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', 'LIB')) S) AS SCHEMAS ,LATERAL (SELECT * ...
0
votes
1answer
40 views

CSV or DB is quicker to read several lines from a file with 1,5 billion lines

I have a program which would read several lines according to the indices of lines within a CSV file with 1,5 billion lines(using c fgets, or is there any tool in C faster than fgets?). Compare with ...
0
votes
2answers
156 views

Case insensitive LIKE in DB2

Is there a way to do a LIKE operation in DB2 against a varchar column in a case insensitive way, without changing the underlying column definition? Or is there a workaround like SELECT * FROM mytable ...
0
votes
0answers
147 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
40 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 ...
1
vote
1answer
220 views

DB2 - Using stored procedure variable in a “CREATE TABLE as (..)” Statement

migrating from Netezza to DB2 we face the following problem: CREATE OR REPLACE PROCEDURE DoStuff() BEGIN DECLARE vStarttime TIMESTAMP; SET vStarttime = now(); CREATE TABLE foo AS ( ...
0
votes
0answers
16 views

IBM DB2 z/OS - Do not UNLOAD DECIMAL rows exceeding a certain length

I have an IBM DB2 table where I want to UNLOAD data from so that I can load it into another DB2 table. The database is running on z/OS. Both tables have the same columns (and types), except one ...
1
vote
2answers
125 views

DB2 window function - running total of X for preceding N minutes worth of rows

Very new to window functions but think I get the idea. I'm trying to get a rolling sum of one column in 10 minute intervals (determined from a separate timestamp column) A working sample might would ...
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
50 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
76 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
114 views

Linked server to DB2 table locks

I have a problem with a SQL linked server connected to a DB2 database on an AS400 IBM iSeries. I configured an ODBC connection from the ODBC Data Source utility. Once configured the ODBC, i created ...
0
votes
1answer
22 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
1answer
69 views

Unable to alter table - reason code is 10

I'm trying to run this: ALTER TABLE AMMLIBT.SAGRPPF ADD UNIQUE (ID) ADD CHECK ( IS_BUDGETABLE IN ( 0 , 1 )) ; Which gives me this: SQL State: 55019 Vendor Code: -7008 Message: [SQL7008] SAGRPPF in ...
0
votes
2answers
117 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
72 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
votes
2answers
441 views

db2 select statement from a table name where the table name is a variable [closed]

I need to select all the table row count from a db2 database. I have a query to select all the schema and table name: select rtrim(tabschema)||'.'||rtrim(tabname) as tableName from syscat.tables ...
1
vote
0answers
31 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
41 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 (...

1
2 3 4 5
14