[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi,

   In DB2, we can declare a temporary table in a Stored Procedure SP1 and use it in another stored procedure SP2, while calling SP1.

Is there a way to do it in Postgresql 9.7?
Please help with an example, if there's a way to it?
Thanks a lot!
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

DB2 10.5 on Windows 2012 R2

I have an alert configured to let me know when a critical or severe error is written to the diag log.  Over the last few weeks I have gotten this severe error twice.

2018-12-05-09.45.32.311000-360 I379110F439          LEVEL: Severe
PID     : 2092                 TID : 2136           PROC : db2fmp64.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: HOST3
EDUID   : 2136
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::getAlertCfg, probe:30
MESSAGE : Internal Health monitor error
DATA #1 : Hexdump, 4 bytes
0x000000EF56F8ED68 : 0100 0000                                  ....

2018-12-05-09.45.32.314000-360 I379551F526          LEVEL: Severe
PID     : 2092                 TID : 2136           PROC : db2fmp64.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: HOST3
EDUID   : 2136
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::getAlertCfg, probe:40
MESSAGE : Internal Health monitor error
DATA #1 : Hexdump, 24 bytes
0x000000EF51D7F960 : 4442 3200 2000 2D00 2030 0020 002D 0020    DB2. .-. 0. .-. 
0x000000EF51D7F970 : 4345 4E54 4B43 4E20                        DatabaseTwo 

2018-12-05-09.45.32.316000-360 I380079F522          LEVEL: Severe
PID     : 2092                 TID : 2136           PROC : db2fmp64.exe
INSTANCE: DB2                  NODE : 000
HOSTNAME: HOST3
EDUID   : 2136
FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::getAlertCfg, probe:50
MESSAGE : Internal Health monitor error
DATA #1 : Hexdump, 20 bytes

Open in new window

0
Update table in database from another database.

I have two databases, DB1(SQL Database) and DB1 (access database). I opened an empty access database and linked to DB1 and DB2, now I want to update table in DB2 using data from DB1. Can someone please help me with the query?

DB1                                                      DB2
TABLE1                                                TABLE1                        TABLE2
ID_field                                                id2_field                      id2_FIELD
property1                                           property1                    ID_field
property2                                           property2


Here the challenge is the receiving table (DB2 TABLE1) does not have the same Id_key (ID_filed)  so I have to use TABLE2.
Any help would be appreciated.
Thank you.
0
db2 export with delimiter set explicit
I execute the db2 command on a windows machine in the db2 command line interpreter shell
I execute a SQL command against db2 and export the result in a delimited ascii file. I want to be able to set the delimiter, default is space with fixed column length.
I start the export with the following command

db2 -tvmf \\somePath\mySQLCommandToExecute.sql > \\somePath\ExportedResults.csv
and the mySQLCommandToExecute.sql file
CONNECT to MYDB USER MYUSER USING MYPW;
With MultipleReferences as(Select distinct coulnm1 , tb1.column2, column3, tb1.column4 from schema1.table1 tb1, schema1.table2 tb2 where column2 > 1 and column3 <> column4 AND COALESCE(tb2.column1,tb2.column2) = tb1.column1 ) select * from MultipleReferences where column4 in('somefilter1', 'somefilter2');
This is only a schematic example of my SQL. As the SQL command is huge (a lot of filters given by a excel sheet) I generate the sql command via a programm and want to have it in a separate file for execution.

As my SQLToExecute is very long I want to have it in a separate file and not directly entered on the db2 command line. So any suggestions like db2 export to test.del of del select tabname from syscat.tables where tabschema ='VSC';
are NOT applicable in my case.
Being able to execute a SQLQuery that is in a separate file is mandatory. Also solution like adding the delimiter explicitly in my SQL command like select a || ';' || b from mytable is not appicable

0
Hi All

I could see that SQL Data Replication is working fine under the same instance (db2inst1) between a source and target DB .

But i have the requirement to replicate the data between two different instances(db2inst1 and db2inst2) which are running on the same DB server, having source DB in db2inst1
and target DB in db2inst2.

May I know the reason why data is not being replicated.

Please let me know if federation is needed for this requirement between db2inst1 and db2inst2 (which are already on the same DB server) , for the SQL replication to become successful.

Kindly provide your inputs.
0
Hi ALL

Good Afternoon!!!

I am facing below error in ASNCLP  while trying to create the Subscription Set.  Below are the commands used and error messages reported:

Repl > SET SERVER CONTROL TO DB DB1

====
CMD: SET SERVER CONTROL TO DB DB1;
====

Repl > SET SERVER TARGET TO DB  DB2

====
CMD: SET SERVER TARGET TO DB DB2;
====

Repl > CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES TIMING INTERVAL 1 START DATE "2018-10-10" TIME "14:35:00.000000"

====
CMD: CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES TIMING INTERVAL 1 START DATE "2018-10-10" TIME "14:35:00.000000";
====


ASN1956I  ASNCLP : The program now generates the script for action: "CREATE SUBSCRIPTION SET".

ASN1955I  ASNCLP : The program will use the following files: "replcap.sql" for the Capture SQL script, "replctl.sql" for the control SQL script, "repltrg.sql" for the target SQL script, and "replmsg.log" for the log file.

--- ASNCLP Version 10.05.07 Build date 2015-12-21 11:26:45

ASN1550E  The replication action "Create Subscription Set" ended in error. The value for the input parameter "source server information" is missing.

ASN1954E  ASNCLP : Command failed.


Do I need to have and source and target DB names(DB1 and DB2) should have same name to resolve this issue ?

Please kindly provide your inputs.

Thanks in advance!!!
0
Hi All

Which snapshot is recommended to analyze the performance issue of Procedure which is doing an update on a table.

Do i need to go for snapshot of application or snapshot of DB.

Please provide me your valuable inputs.

Thanks in advance!!!
0
Hi All

Greetings!!!

I have 2 instances ( db2inst1 and db2inst2) on the same machine. Each instance is having one DB each. I need to copy the data of a set of tables (for example 20 tables) from
instance1 (db2inst1) to instance2 (db2inst2).  This frequency of data copy should be daily once. Every time only Delta data need to be copied from source to target.

What are the possible ways of achieving this.
0
Hi All

Greetings.

I am facing the below Error message in the Application logs:

 Not able to get a Connection for db2Pool

Some few orders are failing with above message.

Below are my DBM configurations.
 Priority of agents                           (AGENTPRI) = SYSTEM                    
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)            
 Initial number of agents in pool       (NUM_INITAGENTS) = 0                          
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)            
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

Can you please give inputs to resolve this Problem.
0
Hi All

Greetings.
Can any one help me to interpret the below Informational message which I found in my diag log file.

2018-08-25-06.03.16.668279-300 I10046620E503         LEVEL: Info
PID     : 1144                 TID : 14068592123456 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE
APPHDL  : 0-8                  APPID: *LOCAL.DB2.180531042846
HOSTNAME: hostname2
EDUID   : 80                   EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::HdrKickPooledAgents, probe:20
DATA #1 : <preformatted>
Waiting for last disassociation from the db

I am not able to interpret it.

Please help me to understand what is the meaning of above message
0
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

I need to get guidance on proper storage of an as400. I believe it is running system 38 (or 36).

Regards, George
0
Hi All

Greeting!!!

in my WCS DB, some of the transactions are failing and throwing the exceptions for the following SQL statement:

INSERT INTO XORDERS (ORDERS_ID, PICKUPPERSON, PICKUPPRSNCNT, DELIVERYTYPE, GRTSTDATEINDI, SHIPPINGMETHOD, MAILSTATUSINDI, STATUS, OPTCOUNTER, LANDMARK, SELLERSTORE, STORESEQUENCENUMBER, FIELD1, FIELD2, FIELD3, ALLSHPCHRG, STORETRREQQTY, STKSHPCHRG, SPLSHPCHRG, MSIBIN) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Probably it might be due to primary or foreign key constraint errors or may be invalid data (Data with wrong data Type ) is received from Application.

My DIAG Level is 3, How to capture the whole transaction details(with actual values) in DB2 so that I can analyze the case more better.


Please kindly provide me inputs on this.
0
HI ALL

Greetings!!!

what is the best approach to copy a schema to a new schema with in same Data Base if I have LOB , CLOB and XML data Types.

I also have generated columns in my schema.

In IBM documentation, it is clear that it (ADMIN_COPY_SCHEMA utility) does not support XML Data , as below.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022035.html 
(Please refer the heading Restrictions )

How to handle this case of copying the schema.
0
Hi All

Greetings!!!

Hope you are well.

I need to copy a Schema from one machine to other machine,

Both machines have Db2 instance of same version (10.5)

But Schema is having data of LOB data type.

Which method is simple to copy the entire schema with structure and data.

I know ADMIN_COPY_Schema only supports on same machine and under same instance .

But db2move utility does not support LOB data type (I think).

Any inputs on this Please.

Thanks in Advance!!!!
0
Hi All

Greetings!!!!

Hope you are doing well.                      

I need a mail alert whenever a DML  (INSERT,UPDATE,DELETE)  statement is executed in my DB2 Data Base.

Do we have any feature in DB2,  through which I can achieve my requirement.

Thanks a lot in advance!!!!
0
TABLE1
store_ID
Relation_ID
Store_Open
Store_Close
Trans_Date


TABLE2
Relation_ID
Relation_Name

TABLE3
Store_ID
Store_Name

TABLE1 is transactional, table 2 is static as is table 3
I need to find all the stores and their names from TABLE3 where the stores opened in 2016. Also all the names where they closed in 2017. But only for a Relation_Name = "MyStore"
I have a query that, without using the transaction dates, gives results, but I'm sure its wrong.  I need to only use the Max record previous to the end of 2016.
(I know the date syntax can change from platform to platform - this is for DB2, or oracle)

The query that almost works is:
Select distinct(A.Store_ID)
,A.Store_Name
from TABLE3 A
join TABLE2 B on A.Relation_ID= B.Relation_ID
join TABLE3 C on A.Store_ID = B.Store_ID
where B.Relation_Name = "MyStore"
and C.Store_Open < '01/01/2017'
and C.Store_Close > '01/01/2017'

How do I only use the Max records of TABLE1 in 2016?
0
Hi Experts,

Is there any way to compare 2 timestamp and pull records from DB2 using query.

 Query wil be having input file which contains basic timestamp .We have to compare this timestamp with the timestamp in process_date column and have to pull all records which happend after this basic timestamp.

How to compare timestamps and pull records ...

Any help regarding this would be helpful.

Thanks in Advance.
0
Hi,

Can anyone advice what is the command to check for total user mailbox and size?

eg.
Sum up
DB1 => 500 Users Mailbox and 300GB
DB2 => 1000 Users Mailbox and 400GB

Tks

Lucky
0
Hi Experts,

Good Day

As per my work , I need to write a DB2 sql query which has to pull records after a specific timestamp..Already in the table there is a column for timestamp(transaction_date). So basically It should compare with this column value and If the transaction-date timestamp is greater than the specific time stamp , It should pull those records.

Specific timestamp will be present inside a text file(/home/user/inputpath/Extracted-date.txt).
How can I write query? input should be taken from the text file and compare with the transaction-date timestamp.

select query where 

Open in new window

Thanks in Advance.
0
PMI ACP® Project Management
LVL 12
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

I want to export data from Mainframe DB2 to Excel through VBA. I am not able to establish connection through following connection string.


Set con = CreateObject("adodb.connection")
Set mrs = CreateObject("adodb.recordset")

With Sheets("Sheet1")

con.Open "Driver={IBM DB2 ODBC DRIVER};Database=<dbname>;Hostname=<hostname>;Port=1433;Protocol=TCPIP;Uid=user;Pwd=password;"

Its giving run time error for data source not found and no default driver installed.
0
Hi All

I have two tables with CLOB data type in a DB2 LUW 10.5 DB.

I want to compare all the columns and rows in the two tables and want to find any mismatches

It looks EXCEPT function does not work for CLOB data types columns.

Any suggestions on this Please.

Thanks in advance.
0
Hi All

I have found some junk characters in my table some thing like small square box.

Can you please guide me how to remove them from the table.

The column data type is CHARACTER(100).

These junk characters are in the middle of character string. I am not sure how they came in to DB.

Thanks in advance!!
0
HI All

I could see in db2top report that Application Transactions are in Roll Back in Progress (30%) like this. There are around 8 Applications like this.

In Diag log, I could see lock escalation messages.

May I know what could be the reason for this?

I am seeing the following message in Diag log:

MESSAGE : ADM5501I  DB2 is performing lock escalation. The affected application
          is named "APPNME", and is associated with the workload name
          "SYSDEFAULTUSERWORKLOAD" and application ID

Any inputs pls
0
Hi All

How to set the
allowNextOnExhaustedResultSet , queryCloseImplicit  and resultSetHoldability  values in db2 LUW Data Base

Can you please kindly provide inputs to change these settings.

My Application is facing the following error:

Invalid operation: result set is closed. ERRORCODE=-4470
0
SELECT COUNT ( DISTINCT cust_code ) AS "Number of employees"
FROM orders;

checking above query at
https://www.w3resource.com/sql/aggregate-functions/count-with-distinct.php

how to display those unique values of  cust_code rather than just count
please advise
0

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Top Experts In
DB2
<
Monthly
>