Go Premium for a chance to win a PS4. Enter to Win

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 All

Greetings.

I have used dynamic cursors in my code. I also have used elseif ladder to avoid following error.

The program attempted to execute an OPEN statement for an open cursor.

I could be able to resolve the above error with elseif ladder but the following statement in my code is not fetching any new record
into V_WC8PAY_ID when i checked in line by line debugging.

fetch c10 into V_WC8PAY_ID;

I could not understand why dynamic cursor c10 is not working.

Please guide me.
ext_data3.txt
0
Veeam Task Manager for Hyper-V
LVL 1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

DB2 10.5 on Windows

I am trying to delete a lot of data from a table.  I created a duplicate of the table with a different name and am trying to move the last years worth of data to it, then truncate the original data and then move the saved data back to the original.

There is an ID column that is set up like this.

ID	INTEGER	NOT NULL	GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER)

Open in new window


When I run this to move the data back

INSERT INTO ORIGINAL_TABLE OVERRIDING SYSTEM VALUE (SELECT * FROM DUPLICATE_TABLE WHERE  DATE(LASTTIME) >= '01-01-2017')

Open in new window


I get this

ERROR [42601] [IBM][DB2/NT64] SQL0104N  An unexpected token "OVERRIDING SYSTEM VALUE" was found following "ORIGINAL_TABLE".  Expected tokens may include:  "<space>".

Open in new window


Everything I have read says the OVERRIDING SYSTEM VALUE should work but it does not.  I am sure it is a simple issue but I have wasted 1/2 a day on trying to figure it out.  Any help would be greatly appreciated!!

Jim
0
Hi,

I have a problem via SQL and into RPG too when I type SUBSTR(xxxx,1,6)
from some reason on my iSeries it requesting to be one space between comma and lengths e.g. substr(xxx, 1, 6).
I know that on other iSeries it's not a problem and on more examples it is not written with these spaces...

This is the error message when I check

Select * from ztrans0p                    
 where substr(trcrd#,1,6) = '516971'        
 Token ,1 was not valid. Valid tokens: ) ,.
   

Message ID . . . . . . :   SQL0104                                            
                                                                               
                                                                               
Message . . . . :   Token ,1 was not valid. Valid tokens: ) ,.                
                                                                               
Cause . . . . . :   A syntax error was detected at token ,1.  Token ,1 is not  
  a valid token.  A partial list of valid tokens is ) ,.  This list assumes    
  that the statement is correct up to the token.  The error may be earlier in  
  the statement, but the syntax of the statement appears to be valid up to    
  this point.                                                                  
Recovery  . . . :   Do one or more of the following and try the request again:
    -- Verify the SQL statement in the area of the token ,1. Correct the      
  statement.  The error could be a …
0
hi all,

Can DB2 has built in Brute-force protection ? what tools /configuration needs for this.
0
hi,

as MySQL configuration only allow one db log for ALL user DB. how about DB2 ? it should has one or more log file for EACH user database inside, right?
0
hi,

any one know what is the replication technology available for DB2?
0
hi,

for DB2, what is the HA tools for DR and HA? what is the tools involved in multi site configuration.
0
SQL0501N  The cursor specified in a FETCH statement or CLOSE statement is not
open or a cursor variable in a cursor scalar function reference is not open.
SQLSTATE=24501

I am facing the above error while executing a Db2 procedure.

Can you please help me to solve this.
0
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
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
Learn Veeam advantages over legacy backup
LVL 1
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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
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
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
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
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
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
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
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
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
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
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
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

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
>