Advertise Here

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

DB2

5K

Solutions

3

Content

i
The collection of articles, videos, and posts on this topic.

3K

Contributors

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

DB2 10.5 on Windows

I have a prod and HADR server.  Last week my HADR server was rebooted due to some windows updates and a mistake (they are not supposed to auto restart my DB2 servers).  Ever since then the HADRservice between prod and HADRis not working.

I restarted the HADR service on the HADR server with this command.

- start HADR
db2 start hadr on db DBName as standby

and checked it with this command.

- check if HADRis running
db2pd -db DBName -hadr
      -
And it looks good.  I go the production server and run this.

- start HADR
db2 start hadr on db DBName as primary

and it comes back in about a minute or two with

SQL1768N Unable to start HADR Reason Code = "7"

I look that up and it states that it is a connection timeout between primary and standby.  I can ping and telnet to the HADR server from PROD but I am not sure what other communication protocols it might use.

Anyone else ever had this issue?

Jim
0
8 Comments
NFR key for Veeam Backup for Microsoft Office 365
LVL 1
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Get you free key
Hi.  I need some help formatting an OS400 DB2 SQL update query joining 2 files.  I'm trying to update an attribute in Table A based on some criteria in both Table A and Table B.  I've tried a bunch of different ways and can't seem to quite get it right.  Here's where I left off...

update tablea/itmpln a set a.nods = 5
  where exists (select * from tableb/itembl b where b.ltcod = 'M' and a.itnb not like 'M%' and a.whid = '1' and b.house = '1' and a.nods <> 0)

This did run but updated about 600 extra records it should not have.  The extra records come from the query ignoring the filter over tableb  ( b.ltcod = 'M' )

I'm thinking I'm missing an intial "where" subquery to create the join but am new to sub-query so not sure how to structure the statement.   thanks
0
3 Comments
I need to know if an IBM(I)  command prompt override program was called, or was the CPP called directly on a command line.  

I need to identify if a command was called after using a command prompt override program was called after pressing F4, or was it called directly by typing all info in on a command line and pressing enter.

I tried sending a *EXT message to the CPP - that worked in all cases but ONE ---  If you type a command, press F4 the message gets sent, the CMD processing program ends, and the CMD UI panels show the command --- however if from here you hit F12 -- the *EXT Message still remains there, then executing the CMD on the CMD line without prompting, the CPP will find the *EXT message from the previous F4, and then think the F4 prompting must of happened -- but it didn't

Any Ideas ?
0
0 Comments
I am trying to export the data of a table on AS400 to another machine through iSeries commands but i am stacked in the middle of the process. I have a stored procedure in which i create the CSV file but after completion i need to transfer this file to another machine (which is of course connected to the AS400).

In the stored procedure, i used the CPYTOIMPF command to export table data to CSV and i wrote the file on the AS400 file system. I don't know if there is an option to write the file directly to another machine.

CALL QSYS2.QCMDEXC('CPYTOIMPF FROMFILE(LIBRARY/TABLE) TOSTMF('/QIBM/UserData/TestFolder/2.CSV') STMFCODPAG(*PCASCII) RCDDLM(*CRLF)'); This step is completed and the file is written on that directory.

Now i need to transfer this file to a web server that is connected to AS400 without after the above command is completed.
Any suggestions?
0
2 Comments
Hi Experts ,
I'm using MS Exchange 2013 STD in DAG.
 
I have been trying to move all the mailboxes from database db1 to db2 i have moved all the mailbox successfully except one particular mailbox . When i start the migration job it starts and says syncing ,but it syncs forever doesn't show any data transferring status.it synced status is always zero.

Please find the attached image for reference . In the screenshot "ajt" is the email address which i'm facing issue to move to different database.

Please help me.
screenshot.png
0
2 Comments
Hi Experts,

I have to alter a serie of programs, that using calls to:
CMALLC, CMINIT, CMRCV, CMSCST, CMSEND, CMSPTR, CMSST and CMSTPN.

I found these objects in QSYS2 so I assume these are IBM supplied programs and now understand that this is SNA related.
Because of an outdated OS I cant install our standard communication tools, so I decided to use a Data Queue to exchange information with 1 other system.

I'm not familiar with this Cxxxxx programs and not sure if I need all of them for the DTAQ solution,
if I look to the source I have to alter, I assume it must be enough to use the CMSEND and CMRCV part?

I already looked to the IBM info, but that's not a big help :-(

Is there more info, or can someone confirm my assumption

Thanks,
0
0 Comments
DB2 10.5 on Windows.

I want to move a large table 15 million rows (about 9 GB)  from TABLESPACE1 (on D: drive) to TABLESPACE2 (on L: Drive).  My problem is that there is only about 2 GB free on D: so when I try to move the table, it fills up D: with the shadow copy (I assume).  So it fails and I have to cancel the move.

So can I control where the shadow table for the SYSPROC.ADMIN_MOVE_TABLE procedure?

Any suggestions would be greatly appreciated.  

Jim
0
7 Comments
DB2 10.5 on Windows

I have a storage group (SG1) set up on 2 drives (T: and Z:).  T: has 4 GB free and Z: has 300 GB free.  All tablespaces are in SG1.  

If I want to add a new tablespace in SG1 and make sure it is on Z: where I have plenty of free space, how do I do that?  Looking at the CREATE TABLESPACE command, I can chose the storage group but I don't see a way to chose the Z: drive?

My issue is that I am running out of space on T: and I have a large table 10 GB that I want to move to the new tablespace.  I just need to make sure it is on the Z: drive.

Thanks!

Jim
0
3 Comments
Hi Experts ,

Getting error mid of restoring . 70% process is done but after that getting below error .  DB 10.5.8

SQL0902C  A system error occurred. Subsequent SQL statements cannot
          be processed. IBM software support reason code: "2"

Please suggest .
Regards,
Dharmesh
0
3 Comments
DB2 10.5 on Windows

I have a logging table that has never been cleared so it has 165 million rows in it.  I added an index on the date column of the table and want to start deleting it day by day.  When I am done, I should have about 500,000 rows left.  Then I will set up a procedure to delete anything over 6 months old going forward.

I have read that you can temporarily turn off logging on a DB2 table, which sounds like exactly what I need for the initial purge.  

Here is an example I found.

db2 alter table <table_name> activate not logged initially
db2 delete from <table_name> where <column_name>='<pattern>' 
db2 commit

Open in new window


So my question is, does the db2 commit turn logging back on?  None of the examples were clear about that.  

Are there any gotchas I should be aware of?  Thank you!

Jim
0
2 Comments
New feature and membership benefit!
LVL 10
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Learn More
Hi Experts,
I'm using exchange 2013 standard.Both of the mailboxserver's  "Microsoft share point search component"  service  eats up almost all the memory,this causes my DB to dismount then i had to restart the servers and was able to mount the db back.  Does any one know what causing it to eat up lots of memory ? do I need to upgrade RAM or Processor  or is it a memory leak ...Please help

Configuration :- 2 Cas server & 2 Mailbox servers running in a dag.
mailboxservers system config :- processor : Xeon CPU E5-2640 @2.5GHZ (2 processors) (in hyper-v)
RAM : 35 GB
Database :- Total 3 database .
DB size :- db1 :- 1.2 TB
                  db2 :- 600 GB
                  db3 :- 2 TB (I'm already on the process to split this db)
also please suggest me if need to do any system up gradation .

Thanks in advance.
Sharaf
0
3 Comments
What is the best way to monitor for queries that run > xx seconds?  I would love to have a table / file that I can look at each morning to see if there are any issues.  I've read about event monitors but I did not see a way to restrict them to a certain time range and we have so many queries that run, I am afraid to capture ever SQL statement that runs.  I think that would blow out my storage pretty quickly.

What is the most common way to do this with DB2?

Thank you!

Jim
0
2 Comments
DB2 10.5 on Windows.

Is there a dependence between reorg tables and reorg index?  So if I do a reorg table, will that automatically reorg the indexes on that table or are the indexes completely separate?  

Same thing other way around, if I reorg the indexes of a given table, will that automatically reorg the table as well?

I want to make sure I am not doing double the work by running reorg index and reorg table on same table.

From what I have read I think they are independent of each other, but my boss disagrees.

Thanks in advance!!!

Jim
0
7 Comments
I have been asked to generate Reports from lots of tables in DB2.

I want to learn to solve writing queries from simple to complex through lots of problems and suggested the solution.
0
4 Comments
DB2 10.5 on Windows

My database is about 300-400 GB.  My full once a week backup is 370 GB.  It is growing at a moderate pace (maybe 10% per month).  Yet when I do daily Delta backups, the file sizes are huge.  Here is what I am seeing.

Sat          Full Backup     371 GB
Sun        Delta                118 GB
Mon       Delta               120 GB
Tues       Delta               122 GB

There is no way my database is growing by 100+ GB per day.  The only other thing I can think of is that there might be a process that is updating a single field on multiple tables over and over again.  Maybe a timestamp or something.

So my question is, is there something about deltas that I don't understand?  Is a delta always 20% of database + actual data changed or some such?  Nothing I have read indicates that but I can't reconcile what I am seeing with what I know so far.  Any suggestions for find the root cause?  

Thank you!

Jim
0
3 Comments
How can I make a the rows into a columns for just one column value .

example : id   Team    date                   comments
                   1    1020    9/11/2017          This is comment 1
                   2    1020    9/11/2017          This is comment 2
                   3    1020    9/11/2017          This is comment 3
                   4    1020    9/11/2017          This is comment 4

Result should be
 1020  9/11/2017  This is comment 1 This is comment 2 This is comment 3 This is comment 4
0
7 Comments
Dear
There is any way to find a reason if insert record into database file not successfully inserted in journal

Thanks
0
5 Comments
DB2 10.5 on Windows.  Using TOAD for DB2 GUI.

This should not be hard, I do it every day in MS SQL.  I have a table I fill with RUNSTATS command if the date of last stats is > 7 days old.

CREATE TABLE MAINT.MYRUNSTATS 
( SQLCMD	VARCHAR(500) );

Open in new window

The data in that table looks like this.

RUNSTATS ON TABLE "db1"."USR_TBL" ON ALL COLUMNS ;
RUNSTATS ON TABLE "db1"."USG_TBL" ON ALL COLUMNS ;
RUNSTATS ON TABLE "db1"."ENTRY_TBL" ON ALL COLUMNS ;

I want to loop through the table and run each of the RUNSTATS commands.  Ideally I would create a SQL job to run this code each night so that my stats are always up to date.  But trying to do this is DB2 is making me pull out my hair (and I don't have any to spare).  Why is this so hard and why are there not more examples?  I know I am whinnying but seriously.  

DB2 DBAs have to be doing this kind of stuff all the time, but I can't find a single comprehensive example.  Just bits and pieces and half of them are CLP and half are for Linux and the rest are for Windows and you can't mix and match them.

Any help would be greatly appreciated.  I will need to do the same for table reorgs and index reorgs too.

Thank you.

Jim
0
5 Comments
I have below requirements like, below query should run on 3 conditions, daily, weekly and monthly.

daily rows should give output everyday, weekly should give output on only Saturday. And Monthly should give output on 1st Business day but if 1st is Saturday then output will come 3rd day, if 1st is Sunday then output should come on 2nd day.

select * from Tablename is giving me below 4 rows

Status  ID   COL1  COL2  
= = = = = = = = = = = = 
Daily   12    100  150.2
Daily   17     90  120.6
Weekly  24    180  50.6
Monthly 25    150  155.2

Open in new window


Now I want to show this data as per above requirements

I got my exact output for daily and Monthly by below SQL but need help on Monthly

Select * from Tablename
where
ID in (12,17) OR --daily
(DAYNAME(CURRENT DATE) = 'Saturday' AND ID = 24) --weekly
(TO_CHAR(DATE(CURRENT DATE)) = '01' AND ID = 25 AND DAYNAME(CURRENT DATE) NOT IN ('Saturday','Sunday')) --monthly

Open in new window


But my Monthly logic not giving me expected output.
0
6 Comments
NFR key for Veeam Agent for Linux
LVL 1
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Get NFR key
DB2 10.5 on Windows.

I am working on a procedure that captures a variable from a select statement and if it is null then it needs to exit.  This is the code.

CREATE VARIABLE v_OLD_ID VARCHAR(15);

SET v_OLD_ID = (
SELECT a
FROM db2.table1
WHERE KEY = 12345);

SELECT v_OLD_ID from SYSIBM.SYSDUMMY1;  -- shows null

IF v_OLD_ID IS NULL THEN GOTO exit;

-- do other stuff

exit:
DROP VARIABLE v_OLD_ID;

Open in new window


When I run this I get this error.

ERROR [42601] [IBM][DB2/NT64] SQL0104N  An unexpected token "v_OLD_ID" was found following "IF ".  Expected tokens may include:  "JOIN <joined_table>".

Not sure what I am doing wrong.  Any suggestions would be greatly appreciated.  

Thanks!

Jim
0
4 Comments
ODBC setup successful; however, connecting from SSIS using ODBC trigger the following error message.


Error [42968][IBM][CLI Driver] SQL1598N. An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968.
0
4 Comments
DB2 10.5 on Windows.

Does it make any difference if you do this

FROM Table1 t
JOIN Table2 tt ON (t.col1, t.col2, t.col3) = (tt.col1, tt.col2, tt.col3)

Open in new window


or this

FROM Table1 t
JOIN Table2 tt on t.col1 = tt.col1 AND t.col2 = tt.col2 AND t.col3 = tt.col3

Open in new window


I tried to Google it but could not explain the question well enough.

Thanks!

Jim
0
2 Comments
DB2 10.5 on Windows.

Trying to reorg some indexes and keep getting

SQL0270N  Function not supported (Reason code = "89").  = REORG INDEX is only supported for nonpartitioned indexes on partitioned tables.

Here is the command I am running.

REORG INDEX DP_IDX03 FOR TABLE DH.TABLE1 ALLOW WRITE ACCESS ;

But non of my tables are partitioned so I don't understand why this is happening.  That is the only reason I can find for this reason code.  Anyone have any thoughts?

Thanks!

Jim
0
9 Comments
Table A is ORDERS.  In this, there is the OrderNo and other columns such as:  total cost, total tax, total discount, total shipping.
Table B is ITEMS.  In this there is order# and rows for each item on an order (1 or more per order).

The problem:
I want my output to only show the total cost, total tax, total discount, total shipping on the first row of each order (there may be multiple rows due to multiple items).

As it is today shown below, but gives "orderDate, customer#, total cost, total tax, total discount, total shipping" on every row.  I only need this once per orderNo.  I'd like those fields to be blank on subsequent rows within each order.

select  tableA.OrderNO, orderDate, customer#, total cost, total tax, total discount, total shipping
 ,tableB.Item#, ItemDescription, ItemCost, ItemSalesPrice
from tableA
   join tableB on tableA.orderno = tableB.orderno

I'm using DB2 for i, but I'm hopeful I could adapt most any SQL query regardless of specific SQL implementation.
0
6 Comments
Can any one help me to get the hardening/ Benchmark document for IBM WebSphere. ?
0
3 Comments

DB2

5K

Solutions

3

Content

i
The collection of articles, videos, and posts on this topic.

3K

Contributors

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.

Advertise Here
Top Experts In
DB2
<
Monthly
>
Hall of Fame
Advertise Here