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

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
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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
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
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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
Hi Team,

According to my work, I have to run 7000+ sql statements in DB2 .I have the java code of the same.But by default DB2 java can only run 1338 dynamic statements at one time.
So what I was doing that Split the input data (7000 + ) into mulitiple txt files and run program more than 7 times .Each time I have to change the input path file.

Now  instead of running program 7+ times and manually changing input path everytime,I m trying to dynamically read all the input files from that folder.
Here I am attaching a part of my program only..only the method to read input file.
Below is the code for single input file case. Assume I have files named as a.txt,b.txt,c.txt.....etc.
Text file contains data in the below format:-
----------------------
abc-465747        
dfr-756372
---------------------
Existing code which executes fine  for single input file

declared input file path
static String fileName="C:\\Users\\john\\Desktop\\policies.txt";

Open in new window


main method.
 public static void main(String[] args)
        
    {
    Pre pd = new Pre();
   
      
    pd.read_Policy_list_file(fileNames);

//other methods as a part of my work
}

Open in new window


read+policy method
public void read_Policy_list_file (String fileName)
		{
			try
			{
				BufferedReader br = new BufferedReader(new FileReader(fileName));
				try {
				    StringBuilder sb = new StringBuilder();
				    String line = br.readLine();

				    while (line != null) 
				    {
				    
				

Open in new window

0
Hi ALL

Good Morning.

I am facing problem with DB2 Federation. I am doing between 10.5 and 9.7 .

create server servername type db2/udb version '9.7' wrapper DRDA authid "db2inst1"  password "password" options(add dbname 'DBNAME');


create user mapping for sourceinstancename server servername options ( remote_authid 'db2inst1', remote_password 'password');

Any iputs please..
0
hi,

Anyone know how many effort /steps to migrate from Oracle 10g to DB2?

We have Oracle platform composite of :

1) Oracle application server.
2) Oracle OLAP server
3) Oracle BI server
4) Oracle BI answer.
5) Oracle database server.

Our code is build upon on java, ActiveX and VBA.
0
hi,

what kind of DB2 Geographic Replication solution we can have?
0
Hello,

I am attempting to add two date paramaters to an existing query using a "between" clause like this:

AND TIMES.ADMINISTERED_TIME between '2017-01-01' and '2018-01-01'

Open in new window


The problem is that this part seems to get ignored (doesn't change the results of the query) when added.  

I do not receive any errors and I have tried various ways of referencing the ADMINISTERED_TIME field with not avail.

The following query (for testing) works as expected:

SELECT * FROM  ORDER_MED_ADMIN_TIMES WHERE ADMINISTERED_TIME BETWEEN '2017-01-01' AND '2018-01-01'

Open in new window


Here is the entire query where the date between clause has no effect:

SELECT
DISTINCT 
ORD.CLIENTID ||' '|| TRIM(CLTLST)||', '||TRIM(CLTFST) AS CLIENT
, Case when varchar_format (stopdatetime, 'YYYY-MM-DD HH24:MI') >= varchar_format (I.CHANGESTAMP, 'YYYY-MM-DD HH24:MI') then 'discharged' else ORD.STATUS end as STATUS
,UPPER(BRAND_NAME) AS TEST
,TRIM(BRAND_NAME)||' ('||TRIM(GENERIC_NAME)||') '||TRIM(STRENGTH) AS MEDICATION
,TRIM(SIG_ACTION)||' '|| TRIM(SIG_DOSE)||' '|| TRIM(SIG_DOSE_UNIT)||' '|| TRIM(SIG_ROUTE)||' '|| TRIM(SIG_DOSE_TIMING)||' '|| TRIM(SIG_DOSE_OTHER)||' '|| TRIM(SIG_DOSE_OTHER) AS SIG
, DATE(SCHEDULEDSTARTDT) AS START_DATE
, DATE(SCHEDULEDENDDT) AS END_DATE
, DATE(I.DISCHARGEDATE) as INPATIENT_DISCHARGE
,TRIM(INSTRUCTIONS) AS INSTRUCTION
,TRIM(STAFF.SMMLNM) ||', '|| TRIM(STAFF.SMMFNM) AS ORDERING_STAFF
,TRIM(STAFF2.SMMLNM) ||', '|| TRIM(STAFF2.SMMFNM) AS 

Open in new window

0
hi,

I am comparing major DB concurrent connection limit, what is the max concurrent connection MariaDB and DB2 can accept ?
0
hi,

I am now worrying about the programming support of DB2 database programming language, you know MySQL's programming language is so bad and the error message return from MySQL always misleading, is the error message return by DB2 meaningful and DB2 DBA easy to debug it ?

what is the name of the DB2 DB programming language btw ?
0
Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

hi,

Did you all experienced any kind of DB2 file corruption on unix/linux OS?
0
hi,

any one has any report on the Least vulnerability database report, which one give the Least vulnerability ?
0
hi,

What cloud solution, DB2 can run well on ? RDS from amazon ?

has to offer good support.
0
hi,

after move to cloud, what clouded Db2 debug solution offer.

have to be help to dig out deep level query problem. e.g, which part of query has problem/slow the whole thing down.
0
hi,

how to montioring to DB2 over the cloud.
0
hi,

how DB2 work with MongoDB ?
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
>