Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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

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
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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
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
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
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
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
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
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
Can any one help me to get the hardening/ Benchmark document for IBM WebSphere. ?
0
DB2 10.5 on Windows

I am moving some tables to a new tablespace and when I try I get an error.  The command I am using is:

CALL SYSPROC.ADMIN_MOVE_TABLE( 'Schema1', 'TableName', 'PRD_DATA','PRD_INDX','PRD_LOB','', '', '', '','','MOVE');

Open in new window


The error I get is:
Error      8/24/2017 10:09:42 AM      0:00:01.515      <link> - DB2 Database Error: ERROR [42603] [IBM][DB2/NT64] SQL0010N  The string constant beginning with "'RUNSTATS ON TABLE  ON ALL COLU " does not have an ending string delimiter.      

Any clues?  I have done a goolge search of course and have not found anything yet.  Thank you!

Jim
0
Free Backup Tool for VMware and Hyper-V
LVL 1
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

DB2 10.5 on Windows.

I have a DB2 database that I am removing a large amount of old data from.  So tables with 130,000,000 rows will be reduced to 5,000,000.  I want to do this quickly and cleanly with minimum impact to storage.  I have read several articles on how to do this but each suggest a different way.

What is the most efficient way to do this?  Should I copy the data I am going to keep to a new table and then drop and recreate the original and then copy it back in?

Or should I use a delete or truncate routine?  If I do it this way, will I need to do a reorg with truncate on the tables afterwards?

Any suggestion would be very welcomed.  Thank you!

Jim
0
DB2 10.5 on Windows

I have a query I am trying to optimize and I want to test a couple of different things.  The issue is that after I run it for the first time the data is in cache so first run is 5 minutes and next run is 15 seconds.  In MS SQL I would clear the execution plan for the query before each run to compensate for that.  Is there something similar in DB2?

Thanks!
0
I require direction. I have never had to create a script before so I have no clue how to create it and run it but essentially I need a script to automate running a query and exporting to a .txt file.

I'm working with DB2 SQL on an IBM Power 8. I need a script that will run the following query:

select vend_num, vend_name
from iasp.vendor
order by vend_num

then output the records to vendor.txt. I have a Windows Server that connects to the DB2 database via ODBC. Seems simple enough but I'm not sure where to start.
0
DB2 10.5 on Windows

Is there anyway to create an index in an online mode?  I want to create a composite index on a large production table and I don't want to lock the table.  The table has about  29,063,052 million rows and is heavily used.

Thanks!
0
Hi everyone,

I would like to list all the tables which are in production and not in test.
I tried with  
          select * from QSYS2.SYSSCHEMAS
but I can't find a way to get the attribute.

Thank You !!
0
DB2 10.5 on Windows using TOAD for DB2 6.3

I am trying to get a list of users and their permissions, especially SYSADM users.  I was told I have SYSADM but I hit an error while trying to create a stored proc so I ran this...

SELECT * FROM SYSIBMADM.PRIVILEGES 
 WHERE AUTHID = 'ME' AND AUTHIDTYPE = 'U';

Open in new window


I get this.



I would expect to see SYSADM as one of my privileges if I were SYSADM.  Am I making a bad assumption?  How can get a definitive list of users with SYSADM permissions.  My thought is no one on the server may have SYSADM level permissions because ever query I run from what I find on Google returns nothing.

Thank you!
0
DB2 10.5 on Windows

We moved some large tables to new tablespaces in production and then restarted the server.  Now we can't log into our HADR server due to reason code 4.  From what I see reason code 4 is

Reason Code 4 = Connection requests to an HADR standby database are not allowed while the replay-only window is active. The replay-only window is active when DDL or maintenance operations are being replayed on the standby.

If the primary db was rebooted before the HADR changes were complete, could it get stuck like this?  I would assume it would just start up again when the primary came back online.

Thanks!
0
Hi,

right  now need a study on any limitation on Oracle Golengate replication and MS SQL AOG for business case that need to setup 2 x site,  active active (not multi master). Then we try to POC on which one is better to use in terms of stability.

how about DB2's active active solution? any idea?

please present your idea.
0
Running DB2 10.5 on Windows

We have a DB2 10.5 HADR server set up that I should be able to use for read only queries.  I can connect to it (using TOAD), but when I do any selects from any table I get the following.

Lookup Error - DB2 Database Error: ERROR [] [IBM][DB2/NT64] SQL1773N  The statement or command failed because it requires functionality that is not supported on a read-enabled HADR standby database. Reason code = "1".

I checked the DB2_HADR_ROS registry entry and it is set to ON as it should be.

Has anyone else run into this?

Thank you!

Jim
0
Is Your Team Achieving Their Full Potential?
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Is there a general script library for DB2?  I am looking for examples of how to set up and run regular maintenance, do restores and backups, and so on.  I can, of course, dig up the info piece by piece and put things together but it would be so much easier to see examples of working scripts.  

Also, is there a general script I can run that will look for common issues and setting in my DB2 instance and give me feedback?  Like this but for DB2:  https://www.brentozar.com/blitz/

Thank you!

Jim
0
I am running DB2 10.5 on Windows.

I have a process that will capture the REORGCHK data for indexes to table so I can go through and pick out the indexes that need reorg based on the REORG column.  My question is, when is it best to just reorg all indexes on a given table?

If I have a very large table with 5 indexes and 3 or them need to be reorged, should I do a reorg all or 3 separate reorg statements for indexes indicated?  Is reorg all faster than one at a time or slower or the same?  

Thank you!

Jim
0
I am running DB2 10.5 on windows.

When I run

CALL REORGCHK_IX_STATS('T', 'ALL')

Open in new window


I get the error

Lookup Error - DB2 Database Error: ERROR [22011] [IBM][DB2/NT64] SQL0138N  The statement was not executed because a numeric argument of a scalar function is out of range.

According to the IBN Knowledgecenter  I should be able to do this.

If scope has a value of 'T', use this argument to specify one of the following values:
A fully qualified table name
The value ALL for all tables
The value USER for all user-defined tables
The value SYSTEM for system-defined tables

Can anyone explain what I am doing wrong?  Thank you!

Jim
0
This is on DB2 10.5 for Windows using TOAD.

How can I save the output of CALL SYSPROC.REORGCHK_IX_STATS('S', 'Schema1');  to a table?

Jim
0
My background is as a MS SQL Server DBA, very new to DB2.

I have a report query that I have in a file and I want to analyse it using db2advis.  I run this

C:\Windows\system32>db2advis -d depsvcs -n MainSchema -i c:\db2advis\sqlfile.in -t  5

And I get this.

execution started at timestamp 2017-07-26-10.55.06.475000
Error.  Could not open this input file [c:\db2advis\sqlfile.in]

Error.  No valid statements were found in this input file or in the
workload table.


In windows I set the security to the db2advis folder to full control by everyone.  Same for the sqlfile.in file.

So how do I get db2advis to actually open and use the file?

Thank you!
0
Hi Experts,

I try to check a value in a SQL where clause, but get the error: CPD4374

On the  Where xxxx in() of the next statement

Exec SQL Select * from my file
Where Field1 = :Parm1
  and Field2 = in(:Parm2)

Field2 is numeric(15,0)
Parm2 is Character(128) and contains  '12,13,45,16,32  '

What is missing?
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
>