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 2 instances ( db2inst1 and db2inst2) on the same machine. Each instance is having one DB each. I need to copy the data of a set of tables (for example 20 tables) from
instance1 (db2inst1) to instance2 (db2inst2).  This frequency of data copy should be daily once. Every time only Delta data need to be copied from source to target.

What are the possible ways of achieving this.
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi All

Greetings.

I am facing the below Error message in the Application logs:

 Not able to get a Connection for db2Pool

Some few orders are failing with above message.

Below are my DBM configurations.
 Priority of agents                           (AGENTPRI) = SYSTEM                    
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)            
 Initial number of agents in pool       (NUM_INITAGENTS) = 0                          
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)            
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

Can you please give inputs to resolve this Problem.
0
DB2 10.5 LUW on Windows Server 2016

I have a few tables in production that have photos (as BLOBs) in the rows.  I want to archive them to an archive server and I have tried SSIS but it is very slow due to the fact that SSIS has to write the JPEG to disk and then read it in again.

So someone suggested that I could create an export file with DB2, move the file to the archive server, and then use the load utility to import it.

I have been able to get the export to work, but not the import.

Here is what I have for the export.

db2 "EXPORT TO 'C:\EXPORT\TEST.TXT' OF DELL LOBS TO 'C:\EXPORT\LOBS' MODIFIED BY LOBSINFILE SELECT * FROM Photos FETCH FIRST 1000 ROWS ONLY"

The FETCH FIRST 1000 ROWS ONLY is for testing since there are millions or records.

When I try to import it I use this.

db2 "IMPORT FROM 'c:\export\test.txt' OF DEL LOBS FROM 'c:\export\lobs\test.txt.001.lob' MODIFIED BY lobsinfile INSERT INTO PHOTO_TEST"

It runs but each row gives me the message...

SQL3229W  The field value in column 3 is invalid.  The row was rejected. Reason code: 1

The columns are
1. ID
2. PHOTO TYPE
3. BLOB of PHOTO
4. LASTTIME

Here is what the test.txt file looks like.

10000002,"JPEG","test.txt.001.lob.0.24442/","2013-10-13-15.40.55.705000"
10000004,"JPEG","test.txt.001.lob.24442.25957/","2013-10-13-15.40.58.112000"
10000006,"JPEG","test.txt.001.lob.50399.26155/","2013-10-13-15.41.00.533000"
0
Hi Experts,

After creating several tables with SQL (because of the naming I have to use), I have in my Data Library files with names as CUST_00001 and PROD_00001 (as expected),
The field names in the files from DB2 site, are numbered CDTA_00001, CDTA_00002 etc.  (also as expected),

I prefer to have the SQL field names in my SQLRPGLE program, so that I can create a readable (maintainable) program, is there a way to manage that?

Thanks
0
Hi.  I am a TSQL user trying to make sense of DB2 SQL on the IBMi.  I need to populate a field with a percentage of a cost in another field, within the same table.  That would be simple enough but I need to filter on field values in 2 separate tables.  I think my problem is understanding the joins in DB2..  I put this together but I'm leery of running this code on my IBMi, even over test data.  
UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE exists(
    SELECT a.itnbr,a.smat,a.co1t,a.so1t,b.vndnr,c.vndnr,c.ittyp,c.b2cocd
        FROM wklibj.itmrvbx831 a                                    
        JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr               
        JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr               
        WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'                                                               
        or  c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')    

Open in new window


This looks like it should run but examples I'm finding online lead me to believe that I need to repeat the filters after my enclosed select statement.  I also looked at using a merge statement but not sure it's a good fit here..?  thanks.
0
We have a DB2 10.5 system (on windows) that we are exporting to a SQL Server 2016 data warehouse on a daily basis using SSIS 2013.  The issue I am running into is that some of the tables in DB2 have BLOB columns (picture data) and when I use SSIS to import that to SQL Server it is very slow.  The main DB2 table has about 25,000 new records per day and it takes about 3 hours to move that from DB2 to SQL Server.

The main bottle neck is that SSIS has to write the BLOB to disk, then read it and store it in SQL Server.  I am averaging about 130 rows per second with both systems on solid state drives.  
 
My SSIS data flow is set up like so...

OLE DB Data Souce connected to DB2 10.5
Data Converison task to change DB2 BLOB datatype to Unicode Text Stream (DT_NTEXT)
Derived Column to fix DB2 date to SQL date issue
OLE DB Data Dest to SQL Server 2016

I had the SSIS BLOB storage on C:\temp at first and that was even slower (about 75 rows per second) and I read a blog about why not to do that.  So I moved the BLOB to a different drive and that about doubled the speed but at 130 rows per second, it takes about 3 hours to process 25,000.

Is there any way to speed this up?  Is there any way to use a memory location to read and write the BLOBs too?

Any suggestions would be much appreciated!

Thanks!!!
0
Hi All

My DB instance is down and is not getting started. I have checked the diag logs and found this below error.

I am seeing the message(SQL1063N  DB2START processing was successful) when i use db2start ,but immediately it is getting crashed and I am not able to see any output
for ps -ef|grep sysc.

ADM0503C  An unexpected internal processing error has occurred. All
          DB2 processes associated with this instance have been shutdown.
          Diagnostic information has been recorded. Contact IBM Support for
          further assistance.

Any good inputs please to start the DB instance.
0
We are using Informatica 10.2 R1 for extract, masking, subset using Informatica's Test Data Manager. We have a large volume of DB2 tables to mask and subset from production on an Iseries and mainframe. The problems we are having are designing entities so that they are small enough to manage.  However, not all parent tables are represented for the child tables especially when we run into circular relationships. Can one entity be large enough to accommodate all 720 tables?
0
LVL 26

Expert Comment

by:Brian B
This is a post. Experts generally don't read the posts area. You should probably ask it as a question using the "ask a question" button.
0
Hi All

Greetings.
Can any one help me to interpret the below Informational message which I found in my diag log file.

2018-08-25-06.03.16.668279-300 I10046620E503         LEVEL: Info
PID     : 1144                 TID : 14068592123456 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE
APPHDL  : 0-8                  APPID: *LOCAL.DB2.180531042846
HOSTNAME: hostname2
EDUID   : 80                   EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::HdrKickPooledAgents, probe:20
DATA #1 : <preformatted>
Waiting for last disassociation from the db

I am not able to interpret it.

Please help me to understand what is the meaning of above message
0
Hi All

I am facing the below error when I am trying to connect to my DB2 DB .

SQL30082N  Security processing failed with reason "1" ("PASSWORD EXPIRED").
SQLSTATE=08001

This User which  I am using is a new user created by root to-day.  

I am suspecting that this error could be due to OS configuration problem not with in DB2.

Please provide inuts
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

On SQL Server 2012, I have two different databases(DB1 and DB2). I have created views on DB2 that select on tables from DB1. I have created a user on both databases that I want to be able to select on the views in DB2 but not select on the underlying tables on DB1. When I select on the view as the new user, I get a error saying I can't get access to the underlying table. The new user has connect access to DB1 and select on the views on DB2 (has select under Securables). Nothing else is set (under owned Schemas or Membership). Both databases have the same owner (SID same on both). I have tried doing the alter database DB1 set DB_Chaining on and granting the user select and/or connect but I still get the error.
0
I need to get guidance on proper storage of an as400. I believe it is running system 38 (or 36).

Regards, George
0
DB10.5 LUW on Windows 2012

I have enabled a DDL audit using events on my production database.  The main purpose was to be able to see what and who might be making changes.  Looking at the activity, I see lots and logs of these commands.

ALTER BUFFERPOOL TABLESPACE1 IMMEDIATE SIZE 16162 AUTOMATIC /* db2stmm */  

Looking back 30 days I am seeing this ran for different tablespaces from 300 - 700 times per day.

The server is running Windows 2012 R2 with 32 GB of memory.

From what I have read the BUFFERPOOL is the cache memory used by a given TABLESPACE, is this correct?  Should it be doing this so often?  If the answer is no, what do you recommend?

Thank you!

Jim
0
I have a piece of code that needs to be converted from DB2 to Oracle.
I'm trying to return all queues & sub queues that fall under the parent queue. Each sub queue can have a sub queue.
It runs fine on DB2 but getting an error message ORA-00904: invalid identifier

This is a sample portion in DB2

select *  FROM OPENQUERY(DB2,'
WITH RPL(LEVEL, PARENT_QUEUE_ID, Queue_ID, NM)
AS
          (SELECT 1
                , ROOT.PARENT_QUEUE_ID
                , ROOT.QUEUE_ID
                , ROOT.NM
           FROM schema.tbl_queue ROOT
           WHERE    ROOT.NM IN(''Auto Current'',''Auto PD'',''Auto Repo'' )
           UNION ALL
           SELECT PARENT.LEVEL + 1
                , CHILD.PARENT_QUEUE_ID
                , CHILD.QUEUE_ID
                , CHILD.NM
           FROM RPL PARENT, schema.tbl_queue CHILD
           WHERE PARENT.QUEUE_ID = CHILD.PARENT_QUEUE_ID
)
select * from RPL
')
0
Hi Expert(s),

Is there a way to write Data (eventually with SQL)  from the AS400-DB2 to a MySQL-DB or Oracle?
0
Has anyone tried to use the DB@BulkCopy command within VBA?  I'm trying to get some coding to work that I found on IBM's site; however, the code is structured for C#.  I'm trying to initialize the bulk copy of data from a table within MS Access to a table on my iSeries.  Both tables have the same column names, but I can't seem to get this working all too well.  When I tried to convert this over from C# to VB, it got very sloppy and this is what I ended up with...

Public Sub copyIntoFTMEDELIGT(ANTHONY_FTMEDELIGT As DB2Connection, FTMEDTEST As DataTable)
    Dim MEDCOPY As DB2BulkCopy = NEW DB2BulkCopy Anthony_FTMEDELIGT
    FTMEDTEST.DestinationTableName = "FTMEDELIGT"
    Try
        MEDCOPY.WriteToServer (Source)
        MEDCOPY.Close()
    Catch ex As Exception
        MessageBox.Show(ex.ToString(), "Exception")
    End Try
End Sub

Open in new window


The local table in Access is FTMEDTEST and the remote table is FTMEDELIGT within the Anthony library.

If someone could help or provide some guidance if this can even be done through a VBA module in access, I'd really appreciate it.

Thank you in advanace.
0
DB2 10.5 LUW on Windows Server 2012 R2

We have a column in all our tables called LASTTIME and it is suppose to update whenever the row is edited and act as a last modified date.  The problem is that when the system was created,, the column was set to DEFAULT CURRENT TIMESTAMP and not AS ROW CHANGE.  It has been this way for years but now they want it fixed.

So I am doing the following.

Add LASTTIME2 ROW CHANGE column
Update LASTTIME2 = LASTTIME
Drop LASTTIME column
Rename LASTTIME2 to LASTTIME
Reorg table

The issue is that some of the tables are very large and it is causing a lot of logging and I have run out of space a few times.  I know I can use the NOT LOGGED INITIALLY command to avoid that but I am not sure of the scope of the command.  It seems to say the command is only active for the first UNIT OF WORK after it is activated.  So in the above chain of commands, if I do NOT LOGGED INITIALLY as the first step will it only be good for the add ROW CHANGE column command or will it be good for all of them?

Should I put it before the UPDATE command since that is what is causing most of the logging?

The IBM documentation is not real clear to me on this.

Thanks!!

Jim
0
IBMi / AS400  Hello Experts, I have a need to pass a URL address to the internet that contains zip codes by radius and return the results.  I have found a pretty good site that I can use to pass this URL address and the results are in a CSV format.   How do I pass the URL and save the results in a DB2 file?
0
Hi All

Greeting!!!

in my WCS DB, some of the transactions are failing and throwing the exceptions for the following SQL statement:

INSERT INTO XORDERS (ORDERS_ID, PICKUPPERSON, PICKUPPRSNCNT, DELIVERYTYPE, GRTSTDATEINDI, SHIPPINGMETHOD, MAILSTATUSINDI, STATUS, OPTCOUNTER, LANDMARK, SELLERSTORE, STORESEQUENCENUMBER, FIELD1, FIELD2, FIELD3, ALLSHPCHRG, STORETRREQQTY, STKSHPCHRG, SPLSHPCHRG, MSIBIN) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Probably it might be due to primary or foreign key constraint errors or may be invalid data (Data with wrong data Type ) is received from Application.

My DIAG Level is 3, How to capture the whole transaction details(with actual values) in DB2 so that I can analyze the case more better.


Please kindly provide me inputs on this.
0
Bootstrap 4: Exploring New Features
LVL 12
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

HI ALL

Greetings!!!

what is the best approach to copy a schema to a new schema with in same Data Base if I have LOB , CLOB and XML data Types.

I also have generated columns in my schema.

In IBM documentation, it is clear that it (ADMIN_COPY_SCHEMA utility) does not support XML Data , as below.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022035.html 
(Please refer the heading Restrictions )

How to handle this case of copying the schema.
0
Hi All

Greetings

I have just created a new table with instance user. But I am facing the below error while doing the select of the same table which i just created.

SQL0204N  "schemaname.table1" is an undefined name.  SQLSTATE=42704

This is the first time I am facing the issue like this.

I have also done the runstats on the table, still facing the same error.

Any inputs Please.
0
DB2 10.5 LUW on Windows 2012

I have some users that are no longer with the company and I need to drop them from the database.  I revoked all of there permissions but they still show up under the SECURITY > USERS list.

I tried DROP USER MAPPING but it gave me the error:   SQL20076N  The instance for the database is not enabled for the specified action or operation.  Reason code = "1".

Even though I have revoke all permissions for them, the Security chief wants them off the user report that we run.

Any thoughts?  Thank you!!!
0
Hi, how can I pass a comma delimited string (or just 1 value) as input parameter to DB2 stored proc? I tried this -

CREATE PROCEDURE VALID_STATUS(IN IN_DOMAIN_ID VARCHAR(4096))
    SPECIFIC VALID_STATUS
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
BEGIN
DECLARE cur1 CURSOR WITH RETURN FOR
select a.ENTITY_ID,a.ATTR_VAL from ENTITY_ATTR a where a.ENTITY_TYP='EMAIL_DOMAIN' and a.ATTR_TYP='TLS' and a.ENTITY_ID in (' || @IN_DOMAIN_ID  || ');
OPEN cur1 ;
END

;

The proc should run as follows - call VALID_STATUS('D1')  (with a single parameter) or call VALID_STATUS('D1','D2','D3','D4') (any no of parameters)
0
Hi All

Greetings!!!

Hope you are well.

I need to copy a Schema from one machine to other machine,

Both machines have Db2 instance of same version (10.5)

But Schema is having data of LOB data type.

Which method is simple to copy the entire schema with structure and data.

I know ADMIN_COPY_Schema only supports on same machine and under same instance .

But db2move utility does not support LOB data type (I think).

Any inputs on this Please.

Thanks in Advance!!!!
0
Hi All

Greetings!!!!

Hope you are doing well.                      

I need a mail alert whenever a DML  (INSERT,UPDATE,DELETE)  statement is executed in my DB2 Data Base.

Do we have any feature in DB2,  through which I can achieve my requirement.

Thanks a lot in advance!!!!
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
>