Questions tagged [db2]

Db2 is a family of relational database management servers developed by IBM. It runs on multiple platforms, including Unix (namely AIX), Linux, IBM i (formerly OS/400), z/OS and Windows.

Filter by
Sorted by
Tagged with
-3
votes
0answers
50 views

DB2 with no backup is in restore pending state [closed]

I have DB2, and it is in restore pending state. I cannot restore cause i have no backup. I have no archive logs.. the system is production system. Is there any way, i can get the system back in normal ...
0
votes
0answers
28 views

db2 How to get the application user from delete events

We use temporal tables to store the history of all changes to specific tables in the db. But we also need the user who made the changes. While on db2 side the user is the application, the application ...
0
votes
2answers
46 views

Add days to date (char column with decimal) in DB2

I have a table named INVOICE_ET which has column of below definition INVDATE CHARACTER 10 result of select INVDATE from INVOICE_ET is as below INVDATE 2021-07-31 In another table INVOICE_DY, I have ...
-1
votes
1answer
20 views

How to assign minimal privileges for create view

In Db2 v11.1 on Linux we have a request to create a new user with access only to the "create view" privilege. What are minimal permissions I have to set in order for the user to be able ...
0
votes
1answer
49 views

Saving results from db2 FINAL TABLE

We have a scenario where we need to add multiple rows to different tables, get the generated primary key ID's, then create more inserts with FK's referring those previously generated ID's, etc. On SQL ...
1
vote
1answer
33 views

Meaning of "Total work" for build indexes during load

I don't think I ever reflected about it, but I'm currently loading a table from a cursor and list utilities show: Phase Number = 3 Description = BUILD Total Work ...
0
votes
1answer
26 views

Downside of adding partitions for the future?

I'm considering moving some of our large log-table to range partitioning for ease of archiving them. They are rarely used for queries, other than support and related stuff. Say my oldest data is from ...
1
vote
1answer
52 views

How to get SQL statement in Db2 database for application that is holding lock on table?

I am trying to get SQL statement that is holding the lock on table. Sample: Create table: create table admin.tab (id int not null primary key, col1 int); Insert data: insert into admin.tab values (1, ...
0
votes
0answers
30 views

Auomatically detect if database needs rebinding after restore?

In our deploy chain, we restore backups from our master environment and migrate the data models according to whatever branch is deployed to the target environment. Occasionally the db2level for the ...
0
votes
1answer
14 views

How to Delete Multiple Rows With Multiple SELECT Statements In WHERE Clause in DB2

In the given inventory table below: SKU COST DATE YYMMDD 202114000 0 210109 202114000 12 210228 202115000 50 210108 202115000 30 210109 202116000 54 210103 202116000 60 210301 202116000 100 ...
1
vote
0answers
34 views

Steps to relocate the primary hadr database to a new server?

Currently we have several databases with a HADR configuration where the primary databases are on a Linux Server "A", with the standby all on Linux Server "B". DB2 version is 9.7. ...
0
votes
0answers
48 views

Copying a single database from DB2 10.5 to DB2 11.5

What is the easiest way to copy a single database (has no large objects) from DB2 10.5 Express-C instance to DB2 11.5 Community Edition instance? I tried this method but obviously db2 restore database ...
0
votes
1answer
97 views

Meaning of LFL and XHDR in Db2

I have seen the following messages in Db2diag.log but I do not know what they are. I cannot find any documentation about LFL or XHDR. I know it is related to HADR and log archiving, but what exactly? ...
28
votes
3answers
4k views

Why is the default page size for databases so small?

In PostgreSQL and SQL Server, the default page size is 8 KB, in MySQL is 16 KB and in IBM DB2 and Oracle it is only 4 KB. Why are these page sizes so small? Is there a historical or memory usage ...
1
vote
2answers
68 views

DB2 Linux authentication fails

I have DB2 Express-C v10.5 instance configured to authenticate against LDAP. The LDAP sever is going to be shutdown and I should configure the same DB2 instance to use Linux authentication. I copied ...
0
votes
1answer
81 views

Creating a dynamic db2 reorg script with in-built delay for concurrency

So I'm new to db2, and some of the syntax and other execution restrictions give me trouble. My DB is using DB2 v11.1.2.2. I've created a reorg script which calls the REORGCHK_TB_STATS and ...
0
votes
0answers
17 views

DB2 Databases vs Schemas [duplicate]

Suppose I have a DB2 database instance that manages a single database with 2 Schemas, say A and B What would be the benefits (if any) of having the instance instead manage 2 databases with one ...
0
votes
2answers
106 views

Cross Database Queries using IBM Data Studio

I'm currently using IBM Data Studio 4.1 to run queries against DB2 LUW database(s). My understanding is that I can do cross database queries by prefixing the database(s) names. For example, database....
0
votes
3answers
401 views

Concat a string to a variable to use as a column name in a query

I have a procedure where I query table a and create variables like this SELECT TRCODE , COLA , COLB , COLC , COLD INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION FROM PR . PREXEC WHERE ...
0
votes
0answers
33 views

Checkopenport - DB2 / shell script sample

at DB2 startup we will check, whether the db2 port is in use. In the client DB2 setup we have only one instance / server. the port is always 50020 - for all servers. our idea - netstat -tunlp | grep &...
0
votes
1answer
264 views

Using same table in a UNION multiple times

I am given a query that is using the same table with same columns and filters multiple times and the only thing is changing is the Type which is given manually. I am thinking there must be a better ...
1
vote
2answers
23 views

What steps we need to perform from DB end in case of storage migration?

What are the impacts / steps from DB2 DB side before VMware SvMotion Storage migration ?
0
votes
1answer
194 views

How do I get DBeaver to do a DB2 UPDATE via Cursor

The following query works when run from the console via db2 command. But fails when running via DBeaver. BEGIN NOT ATOMIC FOR R AS C CURSOR WITH HOLD FOR SELECT ROW_NUMBER() OVER() AS INDEX, ID ...
1
vote
0answers
37 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
0answers
15 views

DB2 for i setting connection specific collation

I read an answer to a question that said you can set connection-specific collation for a DB2 for i connection. However, my Google skills are failing me and I can't find anything about it. Specifically,...
0
votes
0answers
31 views

SQL30080N error points to two documents SC31-6160 and (SC31-6156

If I face this problem: SQL1476N with sqlerror "-30080", then I check this other error: db2 ? sql30080 SQL30080N A communication error "<reason-code>" occurred sending or ...
0
votes
1answer
249 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
30 views

DB2 for i equivalent of MSSQL's Show Query Execution Plan

I'm a DB2 newcomer and I'm hoping that DB2 for i has an equivalent to MS SQL's Show Query Execution path. If so, what's it called, how do I access it, and what permissions do I need to use it? Thank ...
0
votes
1answer
75 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
19 views

Trying to connect to DB2 through WinAutomation, which driver should I use?

I'm trying to connect to DB2 through winautomation, I haven't been able to make the connection by using the options below. I'm using the following connection string format Server=myAddress:...
0
votes
0answers
46 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
2answers
65 views

Extract from DB2 to SQL Server - where is the data in between the two servers?

I am trying to understand what happens when I extract data from DB2 from a SSIS package and load it into SQL Server. My question is: where is the data when it is between DB2 and SQL Server? On my ...
0
votes
0answers
88 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
1answer
372 views

how to drop a db2 database that is in an inconsistent state

I have a DB2 database that suffered a power outage in the storage array, its a test system I just want to kill it and rebuild. db2 connect to mydb SQL1015N The database is in an inconsistent state. ...
0
votes
0answers
39 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
179 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
96 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
276 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 ...
1
vote
3answers
687 views

DB2, How to save all stored procedures as files

How does one extract all stored procedures from a DB2 schema as individual files (without using Data Studio so it can be scripted). I need them all so I can uploaded to source control. I have found ...
2
votes
3answers
145 views

Retain indent formatting when compiling a stored procedure without Data Studio

When I compile a stored procedure using CLPplus console (or even in dBeaver), all indentation is stripped out. Only when I compile in Data Studio is the formatting retained, but I would like to ...
0
votes
1answer
219 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 "...
0
votes
1answer
149 views

Authorization error running db2 command line with db2inst1

I need to enable the COLUMNAR setting in DB2 Express Edition on Docker. For that to work I need to set INTRA_PARALLEL ON at the instance or database level. I connect to the db2 command with db2inst1 ...
1
vote
3answers
57 views

Not able to install any DB2 LUW 10.5 fixpacks

I am currently on Fixpack 1 and am trying to install ANY more recent fixpack. All of them refuse to apply themselves with similar errors. The one below is from Fixpack 2 file v10.5fp2_ntx64_server. ...
0
votes
1answer
60 views

What is the format of EXTERNAL NAME argument?

When a Java class cl with a method mth resides in a package pck, what should I put into the EXTERNAL NAME clause of CREATE PROCEDURE? I tried CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL ...
1
vote
1answer
82 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
71 views

Azure SQL Db to DB2 Sync

I need to automatically synchronize changes made in an Azure SQL database to our DB2 system. Whenever a change is made in Azure, DB2 should also be modified. Azure DB creation is not started and ...
0
votes
1answer
46 views

What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?

What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT? As far as I can tell, they seem to do the same thing. Is there some difference in side-effects, or how they perform for certain inputs? ...
0
votes
0answers
122 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 ...
0
votes
0answers
33 views

Db2 - How to configure monitoring to get all of the SQL statements and authorization IDs for specific table?

On Db2 v11.1 on Linux I have a requirement to collect all of the SQL statements and database authorization IDs that access 5 particular tables. I am trying to configure "event monitor", but ...
2
votes
2answers
137 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 ...

1
2 3 4 5
15