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
1
vote
2answers
21 views

differences between EDU and thread

in db2, there is this command that shows active EDUs (engine dispatchable unit): db2pd -edus there are 2 fields in output:EDU ID and TID according to db2pd-page each are defined like this: EDU ID: ...
0
votes
2answers
24 views

Pending changes from a finished job

I'm getting the following error: Uncommitted changes pending for file MYTABLE in library MYLIB. CPF325E Cause . . . . . : Uncommitted changes are pending for file MYTABLE in library ...
0
votes
1answer
27 views

How to access Multiple DB2 Relational Databases In A Single Query

I need a solution for below: There are Two DB2 instances with two authorization ID on the same server and can be access using same user User1. Example: To access Inst1DB, I use the following ...
0
votes
1answer
30 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
31 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 ...
0
votes
2answers
33 views

How to I get a list of non subscribed users from this database?

I have 2 tables. 1) subscription_types | type | 'type1' 'type2' 2) subscriptions | user | sub_type | subscribed | 'U1' 'type1' 'Y' 'U1' 'type2' 'Y' 'U2' 'type1' 'Y' ...
1
vote
2answers
61 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
2answers
91 views

CHARINDEX equivalent in DB2

I am trying to select the domain from the column where I have email addresses. For example, I have a table email_table and a column email with email addresses as below. johndoe@gmail.com jhefid@...
1
vote
1answer
44 views

DB2 SUM of MAX values, single table

I have looked through many of the questions asked and answered but can't seem to find my answer, even kludging the provided answers into my queries - most usually fail, the remainder provide garbage ...
0
votes
1answer
17 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
35 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'...
-1
votes
2answers
33 views

Get longest strings in DB2 column

I'm trying to get the longest strings in a given DB2 table. I know the max length of the columns from the schema, but I want to generate a report showing the longest actual values. For example, if ...
0
votes
2answers
24 views

How to set column text when creating a view in SQL?

By default, when creating a view, for the Text of each column, if the column is a direct selection from a table/physical file then it will use the Text of that table/physical file's column. If it's ...
0
votes
1answer
40 views

Dropping a table/column automatically drops views using it - how to warn instead?

If I have a view that uses a column, and I remove that column/table, the view is dropped automatically. Is there a setting I can change such that it instead prevents the removal of the column/table, ...
2
votes
3answers
112 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
48 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
50 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?
0
votes
1answer
34 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
40 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
37 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
2answers
37 views

Is it worth duplicating a table for different types of an entity when one should be unique?

In my database, I have a CUSTOMER table. I'm migrating an OPPORTUNITIES table, which contains two different types of opportunity, each having identical columns (e.g. status, reason lost, probability ...
1
vote
1answer
85 views

Error on Datastage when I try to write on AS400

I'm having troubles to operate an ETL process on DataStage. I have the following error log: #TABLE#,0: ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = -332: Msg = [IBM(...
0
votes
1answer
34 views

Are iSeries AS/400 (DB2?), Lotus 1-2-3, and Excel date/time formats the same? [closed]

Are the formats for dates/times from AS/400, to Lotus 1-2-3 that carried over into excel...with the date being the number of days since 1900-1-1 when mistaking 1900 for a leap year as an integer, and ...
1
vote
0answers
128 views

IBM db2 FOR XML equivalent

I've been trying to Query an item with its sublist all in one go and one way is to use for xml and subquery. I've been using this strategy with sql server and works fine. The relationship is Order ...
0
votes
1answer
33 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 ...
-1
votes
2answers
38 views

How to convert DB2 database to Microsoft SQL database [closed]

Is it possible to convert databases from DB2 at Linux to Microsoft SQL at windows ? and what is the correct steps ?
1
vote
2answers
151 views

How can I lock row for reading/updating in DB2?

I am trying to resolve a race condition in my application. I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it ...
0
votes
1answer
33 views

Duplicate entries - condition on second table with join

I have 2 tables (1. PLATE, 2. MOVEMENT). First table contains some duplicate plate numbers. Every plate number (Fields: PLATE_ID, PLATE_COUNTRY, PLATE_NUMBER) has an unique id, which exists in ...
0
votes
1answer
211 views

DB2 select from final table update gives SQL0199 error

Goal: I am trying to retrieve records that were affected by UPDATE query. Based on IBMs article I can use the following query: SELECT empno, salary FROM FINAL TABLE (UPDATE employee SET salary = ...
0
votes
1answer
714 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
2answers
119 views

What's default path for “db2move export” command's output

What's default path for "db2move export" command's output in Linux RedHat 7.2 and how to change it to specific folder?
0
votes
1answer
61 views

DB2 start tuning needs privilege

When I run "start tuning" by db2 data studio it gives me the below message. This still happens when I run it under user "db2inst", the default user which I configured when I installed db2 on RedHat 7....
0
votes
2answers
146 views

How can I create rows without an underlying table in IBM DB2 SQL?

In T-SQL, I can execute the following: SELECT * FROM (SELECT 1 AS n UNION ALL SELECT 2 AS n) AS t; In IBM DB2 SQL, this fails with: [IBM][System i Access ODBC Driver][DB2 for i5/OS]...
0
votes
2answers
161 views

Multiple queries in merge into statement

I have a scenario where i want to insert record if it doesn't exist in DB2. If it already exists update is_active column to 0 of the existing row and insert the new row with is_active as 1. I cannot ...
0
votes
1answer
105 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
votes
1answer
38 views

Is the index created as soon as data is inserted into a table? [closed]

For example I have a table and I created an index on ID column which generates numbers every time I insert a row. If I only insert 5 rows does index tree start to be created or is there a threshold of ...
2
votes
1answer
55 views

DB2: Left Join Over-ridden by Where clause?

I have a query that uses a left join. There are some instances where no matching records will be present in the joined table but I still want all the records from table A that match to be returned. ...
1
vote
1answer
42 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
47 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
92 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 ...
0
votes
2answers
44 views

DB2 multiple rows with the same value

This is my first time posting in stackexchange, and I hope I did my due diligence for this question. I'm working on a problem that I just can't seem to wrap my head around. Consider this scenario of a ...
0
votes
0answers
42 views

Counting up date - DB2/SQL

A table is given with the columns customer, entry_date, exit_date. I need a query that shows me each customer and counts up the entry_date in 10 day intervals, but only up to the exit_date. The ...
0
votes
0answers
81 views

Is it possible to implement automation for Oracle and DB2 with Jenkins and or Ansible?

I'm curious if anyone has successfully used Jenkins and/or Ansible for automating routine tasks for Oracle and DB2 databases. From what I've looked up online options seem very limited. So far ...
1
vote
2answers
36 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
47 views

Using a DB2 merge with conditionals for matches?

Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work. The logic I'm looking for is this: If there's a match AND the ...
-1
votes
1answer
40 views

Root node creation in db2 xml

i am able to create the xml node in db2 but all the node creation i need under root node. below code i have written as: CREATE FUNCTION abc.sample(v_pf_wkstn_oid_sh smallint, ...
0
votes
1answer
241 views

SQL Server - Query Over Linked Server to IBM DB2 throws an error

We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server. It's been working great for a long time, but over the weekend they upgraded their ...
0
votes
1answer
145 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 ...
0
votes
1answer
34 views

OS killed some of databse demons

I have 2 HP servers with CntOS 6 as OS and DB2 10.5 as Database. there 2 servers are HADR. standby server suddenly rebooted and when i checked log it said that OS had killed 4 db2 demons. IBM....
2
votes
0answers
66 views

Can we prevent creation of directory NODE0000 during db2 rollforward db <db_name> end of logs with overflow log path option?

I am using command rollforward db ABCD to end of logs overflow log path ('/db2backup/logs') to apply DB2 archived logs to my staging database. My staging database is in rollforward pending state. ...