DB2 10.5 on Windows Server 2012

I need to write a stored proc to search a customer database on multiple, optional parameters.  Lets say firstname, lastname, cust_num.  So here is what I have..

CREATE PROCEDURE DBAUtil.Jim_Search(
     IN p_first_name VARCHAR(50),
     IN p_last_name VARCHAR (50),
     IN p_cust_num INTEGER)
SPECIFIC  DBAUtil.Jim_Search
LANGUAGE SQL RESULT SETS 1

BEGIN

DECLARE v_dynamicSql varchar(5000);
DECLARE c_search CURSOR WITH RETURN FOR v_dynamicSql;

SET v_dynamicSql = 
'SELECT * FROM DBAUtil.CUSTOMER WHERE 1=1 ';

IF p_first_name IS NOT NULL THEN
    SET v_dynamicSql = v_dynamicSql || ' AND UPPER(FIRST_NAME) = UPPER(' || p_first_name || ')';
END IF ;   
     
 IF p_last_name IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND UPPER(LAST_NAME) = UPPER(' || p_last_name || ')'  ;  
END IF;

IF p_cust_num IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND CUST_NUM = ' || cust_num || ' ';
END IF;

SET v_dynamicSql = v_dynamicSql || ' ORDER BY LAST_NAME, FIRST_NAME';
 

PREPARE v_dynamicSql FROM v_dynamicSql;
open c_search;

END;

Select all Open in new window

It compiles but when I run it like so

CALL DBAUtil.Jim_Search (NULL,'YOUMANS',123)

Select all Open in new window

I get this

Category	Timestamp	Duration	Message	Line	Position
Error	12/27/2019 10:53:15 AM	0:00:00.020	<link> - DB2 Database Error: ERROR [42703] [IBM][DB2/NT64] SQL0206N  "YOUMANS" is not valid in the context where it is used.	11	0

Select all Open in new window

So I know my sql string is getting messed up but not sure how.  Is there a way to print the v_dynamicSql string that it is trying to run?  I have tried a couple of ways but no luck.

Is there a better way to do this?

Thanks!!!

DB2 10.5 on Windows.
I did normal windows patching and reboot this morning and now I am getting this error in the DIAG Log.

2019-11-07-07.09.10.427000-360 I6616701F603         LEVEL: Severe
PID     : 1768                 TID : 9092           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000
APPHDL  : 0-15306
HOSTNAME: DB03
EDUID   : 9092                 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeAppServices::InterruptAppByIndex, probe:2861
MESSAGE : ZRC=0x80050810=-2147153904=SQLE_RC_INVALID_AGENT_INDEX
          "Agent index is invalid"
DATA #1 : String, 55 bytes
Ignoring user interrupt that targets system application
DATA #2 : unsigned integer, 2 bytes

Select all Open in new window

When I google it I don't get any results.  Anyone have any thoughts?

Hi, I'm trying to retrieve order number, status, customer#, customer name from our order master and customer master when the customer is on our preferred customer file.  This runs but returns nothing when it should.  Where am I going wrong?   Note: CXSPRF is a preferred level then multiple customer are assigned to it.  So I want all custest1 level where the customer number from the order master is assigned to it and active.

Select a.CHONUM,a.CHOSTS,a.CHOCUS,b.CCNAME                              
from CORDER a, CUSTMST b                                        
where a.CHOCUS = b.CCNUM                                        
  and (a.CHODTE between 20190101 and 20190931)                  
  and EXISTS                                                    
    (SELECT * FROM CCXCPRF c WHERE ((c.CXSPRF = 'CUSTEST1' AND  
                                                                        a.CHOCUS = c.CXSCUS)  AND  
                                                                        c.CXSSTS = 'A'))          


Thank you in advance!!
Lynn

DB2 10.5 on Windows Server 2012.

I am trying to do a simple backup and restore of a small database from one server to another and I can't get it to work.  Here is what I am doing.

Server 1 has a small database called TOOLSDB I run this.

DB2 backup db TOOLSDB ONLINE to X:\DBBackups include logs

Select all Open in new window

And I get this a backup completed with timestamp xxx and a backup file in the X:\DBBackups folder

I copy the backup file to Server2 and run

db2 restore db TOOLSDB FROM X:\DBBackups

Select all Open in new window

It completes with RESTORE DATABASE comm completeld successfully, so then I run

db2 rollforward db TOOLSDB to end of backup

Select all Open in new window

And I get missing log file S0000427.LOG error.
Why??  when you do a backup with include logs shouldn't everything you need be in the backup file?  What am I doing wrong?  

Jim

Hi All,

I have the below query, where I'm trying to use a field I created in a table, which is a combination of values from 3 other columns.  I tested the logic of the combination of values and that works fine.  When I go to use that new field in a join to another like field in another table, I get an SQL0802- Data conversion or data mapping error...

Any help on getting this corrected, will be greatly appreciated.  

SELECT  INPOL100.CMPNO AS COMPANY   
	, INPOL100.PSTAT AS STATUS   
	, INPOL100.PURCH AS "PO NUM"   
	, INPOL300."LINE#" AS "PO LINE NUM"   
	, INPOL100.PODDT AS "PO DATE"   
	, INPOL300.PORDT AS "LAST RECEIPT DATE"   
	, INPOL300.SDESC AS "PO DESCRIPTION"   
	, INPOL300.PRDNO AS "PRODUCT NUMBER"   
	, INPOL300.P3OR1 AS "ORG CODE 1"   
	, INPOL300.P3OR2 AS "ORG CODE 2"   
	, INPOL300.ECOST AS "$ / UNIT"   
	, INPOL300.P3QOR AS "QUANTITY ORDERED"   
	, INPOL300.P3QAC + INPOL300.P3QOR AS QUANTITY_RECEIVED   
	, INPOL300.P3QRJ AS "QUANTITY RETURNED"   
	, INPOL300.P3QOR - (INPOL300.P3QAC + INPOL300.P3QOR) + INPOL300.P3QRJ AS OPEN_QUANITY   
	, APACP10X.ACGLA AS "GL ACCOUNT"
FROM 
	((LOGICLIB.INPOL100 INPOL100
	INNER JOIN
	LOGICLIB.INPOL300 INPOL300
	ON (INPOL100.PURCH = INPOL300.PURCH))
	INNER JOIN
	HERBERT.APACP10X APACP10X
	ON (INPOL100.PURCH = APACP10X.ACPUR))
	INNER JOIN
	RMSGL800.VALMAST VALMAST
	ON (APACP10X.ACGLA = 

Select all Open in new window

…

Experts,
Our software contains ratesheets. They are displayed in the software as traditional grid with an X axis, Y axis and all the values.
I am trying to write an select statement so I can export them.
I expected to find a single table and would need to PIVOT.

Instead, I find the data in 3 tables (Columns, Rows, and Cells)

RATE_COL
Sheet_ID
Col_id   (typically 10 columns)
Col_Label

RATE_ROW
Sheet_ID
Row_ID  (typically 100+ rows)
Row_Label

RATE_CELL
Sheet_ID
Row_ID
Col_ID
Cell_Value


Pretty sure I need to Pivot RATE_COL, but unsure how to incorporate the rest of the data
I am lost.. and need some help

I am querying against a DB2 database

We are upgrading our DB2 LUW environment.  Because we are moving 32bit to a 64 bit Windows, the Server names are changing.  

I have the need to provide a Script to our Desktop Support Team Containing the DB2 unCatalog \ Catalog Statements so they can push the changes to the Windows 7 and Window10 laptops in our Enterprise environment.

I know how to write the Catalog Statement scripts but I am struggling with how to evoke a DB2 Command window in the script so that the DB2 catalog statements will run.

I am creating a process to delete any data > 6 months old from a large table.  It will run on the first of each month.  The amount of data I am deleting can be greater than 10,000,000 rows so I would like to delete 10,000 at a time until they are all deleted.  In SQL Server I would write something like this...

WHILE (SELECT COUNT(*) FROM TABLE_ONE WHERE LASTTIME < GETDATE()-6 MONTHS) > 0
BEGIN
     DELETE TABLE_ONE WHERE MAIN_ID IN (SELECT TOP 10000 MAIN_ID FROM TABLE_ONE WHERE LASTTIME < GETDATE()-6 MONTHS)
END;

Select all Open in new window

How would I do similar operation in DB2?  Thanks!

Using DB2 for z/OS 10, I'm have a column (ADDRESS3) in a table that contains city, state, and zipcode all in one column. But, the formatting is somewhat "free-form". (see code below)

I can successfully parse the city by taking everything before the comma. Using only DB2 SQL, how would I parse out the state and zipcode?

SELECT ADDRESS3,
       TRIM(SUBSTR(ADDRESS3,1,LOCATE(',', ADDRESS3)-1)) AS CITY
  FROM MYTABLE

ADDRESS3                            CITY         
----------------------------------  -------------
GRANADA HILLS       ,CA 91344       GRANADA HILLS
SIMI VALLEY,  CA 93065              SIMI VALLEY  
BUENA PARK          ,CA 90621       BUENA PARK   
SHERMAN OAKS, CA 91423              SHERMAN OAKS 
GLENDALE            ,CA 91203-2089  GLENDALE     
VENTURA             ,CA 93002       VENTURA      
HAWTHORNE           ,CA 90250       HAWTHORNE    
PASADENA          ,CA 91185-2594    PASADENA     
BEVERLY HILLS       ,CA 90211       BEVERLY HILLS
ARCADIA             ,CA 91007       ARCADIA      
WALNUT CREEK        ,CA 94596       WALNUT CREEK 
INDEPENDANCE        ,CA 93526       INDEPENDANCE 
LOS ANGELES         ,CA 90017       LOS ANGELES  

Select all Open in new window

Ultimately, I'd like the query results to look like this:

CITY          STATE ZIP
------------- ----- -----
GRANADA HILLS CA    91344
SIMI VALLEY   CA    93065
BUENA PARK    CA    90621
SHERMAN OAKS  CA    91423
GLENDALE      CA    91203
VENTURA       CA    93002
HAWTHORNE     CA    90250
PASADENA      CA    91185
...

Select all Open in new window

Thanks!

DB2 9.7 on Windows Server 2012

My user on the database has all the permissions possible but it still can't do anything.  Here are the permissions I have.

 
But if I try to do almost anything I get a permissions error.  I try to run LIST APPLICATIONS and I get this

SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User
ID: "JYOUMANSSO".

Any thoughts?  I should be able to do anything with the permissions I have I thought.

Thanks.

Jim