[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

DB2 10.5 on Windows

My department actually has some training budget for the upcoming year and I have been asked to submit some thoughts on what would be beneficial for me to attend.  I was looking at IDUG but it looks like it is geared towards z/OS and not windows (I did not see a single subject with windows mentioned in the description for this years IDUG).

Does anyone know of any good conferences or seminars that would be good for a SQL Server DBA who is now working with DB2 10.5 on Windows?  Maybe something near a beach?

Thank you!

Jim
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

DB2 10.5 on Windows

Is there any way to see exactly what gets written to a DB2 online incremental or delta backup file?  Not the actual data but what tables and/or table spaces are involved?  My full online database backup is 330 GB and my incremental and delta backups are in the 170 GB range.  That is half the database.  I think this is due to the number of BLOB fields in the database but I would like to be sure.

Thanks!

Jim
0
DB2 10.5 on Windows Server

If I have something like this...

START TRANSACTION
     CALL WEB SERVICE; (can take up to 8 seconds)
     UPDATE TABLE1;
     UPDATE TABLE2;
     DELETE FROM TABLE3;
COMMIT TRANSACTION;


How would DB2 handle the tables during the life of the transaction?  Would it only lock a table when the change is made or would if lock the tables at the start of the transaction?  I am not familiar with DB2 transactions and while the IBM info pages describe how they work, none of it answers this question.

I am making the assumption that they are similar to transactions in SQL Server although I do see some references to SAVEPOINTS which I am not sure I understand.  I think that might not apply to DB2 on windows.

Thank you so much for any help in advance.

Jim
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
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
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
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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

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
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

One of our developers is attempting to do an "Insert" into a view.  They are connecting in via ODBC.

I gave the user profile *ALL authority to the View but I am getting the error:

Message: [SQL0551] Not authorized to object VSTOWERINT in DCTEST type *FILE. Cause . . . . . :   An operation was attempted on object VSTOWERINT in DCTEST type *FILE.  This operation cannot be performed without the required authority. Recovery  . . . :   Obtain the required authority from either the security officer, the object owner, or a user that is authorized to the QIBM_DB_SECADM function. If you are not authorized to a logical file, obtain the authority to the based-on files of the logical file. Try the operation again.   Processing ended because the highlighted statement did not complete successfully

I figured I needed to give the user access to the file that the view was based on...but I gave the user *ALL access to this file too and we are still getting the same error.

Does anyone have any insight into why we would be getting this error - even though we gave the user *ALL access?

Thanks in advance
0
Hi,

Right now want to research about how vulnerability each major DB has :

1) MS SQL
2) MySQL
3) MariaDB
4) Oracle
5) DB2.

Other than NIST database : https://nvd.nist.gov/vuln/search.

What other source I can search on the number of vulnerability of each product in last 3 years and the result make sense ?  the source has to be trust able !
0
SELECT UPPER(COLUMN1)
FROM SCHEMA1.TABLE1

SELECT UPPER(COLUMN2)
FROM SCHEMA2.TABLE2

i have 100 records in column 1 and column 2 with roughly half capitol words(HONDA etc.) and rest of half small words(nissan etc.)

when i do UPPER character function

i expected to see 50 records with values like NISSAN


when i do LOWER character function
i expected to see 50 records with values like honda

but i got only 2 records.

can you please advise how to fix this
0
hi,

what kind of encryption DB2 is offering? what tier are they doing to protect?
0
hi,

for DB2, any feature/way to move all DB2 files on RAM , not just logical table / objects?
0
hi,

Automatic storage management is only logical structure only and not related to what oracle ASM can offer, it can't load balancing data based on need, right?

also any limitation on using that ?
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
>