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.

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

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!

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.

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

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

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

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?

Environment:
IIS:8.0
.net4.5
windows server 2012

My website sometime will show this error in the web server .

A process serving application pool 'XXX' suffered a fatal communication error with the Windows Process Activation Service. The process id was '6052'. The data field contains the error number.

please help.

We have problem with the application run DB2ResultSet.Read(). sometime will get EROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000.

Database : DB2 for Linux, UNIX and Windows V10.5
Client: Windows 7 64bit

Method:
public int EventGetEvSegmentCnt(string SegmentID, string strEvntGroup)
        {
            int strGroupCnt = 0;
            string strSQL = string.Empty;
            DB2ResultSet objRs;
            if (string.IsNullOrEmpty(SegmentID) || string.IsNullOrEmpty(strEvntGroup))
            {
                strGroupCnt = 0;
            }
            else
            {
                strSQL = " SELECT COUNT(EVNT_CODE) AS EVNT_GROUP_COUNT FROM E_SEGMENT_EVENT WHERE C_SEGMENT_ID = " + SegmentID + " AND EVNT_GROUP = " + strEvntGroup;
                Common.DatabaseHelper helper = new Common.DatabaseHelper();
                objRs = helper.ExecuteResultSet(strSQL);
                if (objRs.Read())
                {
                    strGroupCnt = 0;
                }
                else
                {
                    strGroupCnt = int.Parse(objRs["EVNT_GROUP_COUNT"].ToString());
                }
            }
            return strGroupCnt;

        }

Error Message:
[Information]      System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> IBM.Data.DB2.DB2Exception (0x80004005): ERROR [24000] [IBM] CLI0115E  Invalid cursor state. SQLSTATE=24000
   at …

Microsoft provides a development SQL server that can run on your laptop or PC for developers to work against. I can put a Northwind database on such for example, and devise queries against it. I am looking for similar tools that I can do so for these database formats, if there is such an animal...

DB2
Oracle
SAP
AS400

I have the need to get a BOM with all components , currently using Tc. CK86 , but this doesn't give enough information, so I want to do this by SQL, I come from oracle background and dont know how could I get this done in DB2 R/3, I do not have access to query builder or quickview , but i do have read access via SQL, I'm currently trying to figure out a way of getting this info using tables :

MAST Material to BOM Link
STKO BOM Header
STPO BOM item

Does any of you  have a solution for this?

In Oracle I did something like the following:

SELECT DISTINCT LEVEL
	,sys_connect_by_path(msil.segment1, ' @ ') AS "BOM TREE"
	,msi.segment1
	,lpad(' ', LEVEL, '') || msil.segment1 Cod_Component
	,msil.item_type
	,msil.description Desc_Component
	,BIC.component_quantity
	,msiL.primary_unit_of_measure
FROM mtl_system_items msi
	,bom_bill_of_materials bom
	,BOM_INVENTORY_COMPONENTS BIC
	,MTL_SYSTEM_ITEMS MSIL
WHERE msi.organization_id = 332
	AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
	AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id
	AND bom.bill_sequence_id = bic.bill_sequence_id
	AND nvl(bic.disable_date, sysdate) >= SYSDATE
	AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
	AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
	AND msil.inventory_item_status_code = 'Active'
	AND msi.inventory_item_status_code = 'Active' connect BY prior bic.component_item_id = bom.assembly_item_id
	START
                  WITH msi.segment1 = trim(:parte)
        ORDER BY 2

Select all Open in new window

I need to combine address fields(addr_1, addr_2, City, St_cd, zip) into new Mail_Address field where most of the time the addr_2 contains null using DB2

In sql server I can do
rtrim(ltrim(a.addr_1)) + ' ' + rtrim(ltrim(isnull(a.addr_2,''))) + ', '  + rtrim(ltrim(a.City)) + ', ' + rtrim(ltrim(a. St_cd)) + ', ' + rtrim(ltrim(a.zip))
but unable to use in DB2.

I've tried a.addr_1 || '' '' || a.addr_2 || '', '' ||  a.City || '', '' ||  a.St_cd  || '', '' || a.zip  which only return a value in the new field if all fields are not null

I've also tried
Coalesce(a.addr_1,'') || '' '' || Coalesce(a.addr_2,'') . This only returned what was in a.addr_1 even if a.addr_2 wasn't null and I couldn't add the city, st_cd or zip to the coalesce