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.

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

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

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

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;

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

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

Can any one help me to get the hardening/ Benchmark document for IBM WebSphere. ?

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

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.

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

Select all 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!

I am trying to write a function in DB2 and the function will parse a certain string based on delimiter and return as seperate fields .
The string I should pass will come from another query ,

example :
CREATE FUNCTIONSPLIT_STRING (varchar string)
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
 
   RETURN values (tempstring,tempstring1);--
END;

Now can I call this function and pass a set of strings like select somestring from table ? or use the same query select somestring from table in the function and iterate through it ?

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