The tag has no usage guidance.

learn more… | top users | synonyms (5)

6
votes
7answers
4k views

On DB2, who updated a record and what did the record look like before the update?

(SQL Server 2008 R2 Standard, database under full recovery) I have a table with fields id, firstname, lastname. A statement is executed: insert into dbo.sometable (id, firstname, lastname) values ...
5
votes
1answer
2k views

SQL Server OPENQUERY vs Regular Query against AS400

I log into a SQL Server database which has an AS400 server as a linked server. I've discovered that running queries against this AS400 server is MUCH faster if I run the select in an OPENQUERY... ...
5
votes
1answer
2k views

How do I resolve this data source connection issue with Cognos?

We have (fairly recently) installed IBM's Cognos 10 as our business intelligence reporting tool. Most of the databases that we report over are DB2 9.7 LUW Enterprise Edition. We installed the DB2 ...
5
votes
1answer
72 views

Measure Performance for INSERT, UPDATE and DELETE commands on DB2

I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day. I insert rows to this table via java-JDBC INSERT commands and they ...
3
votes
5answers
948 views

SQL Query with ODBC, Joins and Group Bys

I am having issues with the statement below. The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i). The query is ...
3
votes
2answers
19k views

How can I enable distributed transactions for a linked server?

I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver. I'm trying to enable distributed transactions. I ...
3
votes
2answers
2k views

DB2 error - SQL Statement too long or complex

Getting this error in a DB2 production system: ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0101 - SQL statement too long or complex. The statement has a large number of OR ...
3
votes
2answers
4k views

Why cannot I call a table function in iSeries DB2 that I just created?

Using iSeries Navigator 'Run an sql script' window I created a new table function. When I go to the Functions branch under the schema where it was created, I see the function among the other few. But ...
3
votes
1answer
743 views

What is object Server?

As I was reading article I came across IBM's site about object Server. What actually is Object Server and what are it's uses? Can this be explained for a beginner?
3
votes
1answer
101 views

How do I perform XPath query on a XML column on DB2 for i?

I have an XML column in a table. I would like to retrieve only the first child element from the XML document in each row (i.e. the result of an XPath expression /root/someVal[1]) and return in my ...
3
votes
0answers
34 views

AS/400 + DB2 to MongoDB?

I'm not seeing a whole lot of info on the subject, so I figured I'd ask people who might know more about it than I do: Is it possible to replicate data from an AS/400 + IBM DB2 instance to MongoDB? If ...
3
votes
0answers
113 views

How can I generated a list of all DB2 Objects including Last Modified Date of object on AS400? [on hold]

I am going through an audit, of an AS400 application. AS400 has a DB2 database, and I need to generate a list of all DB2 Objects/Tables including their last modified date. Can anyone help me? Thanks ...
2
votes
4answers
8k views

UPDATE … FROM … syntax for iSeries for a MS SQL Server developer

When I wanted to update a table to the values from another table in SQL Server I would use following syntax: UPDATE t1 SET t1.a = t2.a FROM t1 INNER JOIN t2 ON t2.b = t1.b What would be iSeries 7 ...
2
votes
2answers
164 views

Not all columns show up on select * on DB2 for IBM i

My client has a table on their IBMi with a foreign key to another table. It's not showing up with a SELECT * FROM lib.table. However, if I specifically mention it in the column list. It does show up. ...
2
votes
3answers
2k views

Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure

After reading 'Defining an SQL procedure' section of DB2 UDB for iSeries SQL Programming Concepts V5R2 and 'Returning result sets from stored procedures' section of i5/OS Information Center, Version 5 ...
2
votes
1answer
6k views

How to loop through AS400 table with cursor

I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for ...
2
votes
1answer
149 views

indexing strategies

I am confused by indexing strategies advocated by one of the database products. Database is "DB2 for i" (iSeries/AS400). Please see following SQL and index advised by the product for the SQL. There ...
2
votes
1answer
298 views

How efficiently does iSeries DB2 use index with WHERE <>?

MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries. I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects ...
2
votes
2answers
1k views

Syntax changes between DB2 for i (DB2/400) v5r4 and v6r1

Does anyone know where can I find list of syntax changes between DB2 for i v5r4 and v6r1? We are currently migrating a backing database for an application from DB2 for i v5r4 to v6r1 and found out ...
2
votes
1answer
258 views

How to resolve foreign key references across different databases (iSeries and SQL Server)

I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework. Originally, I was lead to believe that there ...
1
vote
1answer
2k views

Testing if temp table exists in iSeries SQL stored procedure

What would be the syntax for testing if there is already a global temporary table in DB2 for IBM i 7.1? I tried this after DECLARE GLOBAL TEMPORARY TABLE: SELECT * FROM QSYS2.SYSTABLES WHERE ...
1
vote
2answers
1k views

SELECT * FROM INFORMATION SCHEMA on V5R3M0

I have access to a client's V7R1 AS/400 and I can run queries like SELECT NAME FROM INFORMATION_SCEMA.TABLES and SELECT COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, ...
1
vote
1answer
18 views

IBM i Access ODBC driver returning only first letter of column names

I'm new to DB2 and am connecting to a DB2 for i (V7R1) database using unixODBC and the IBM i Access ODBC driver for Linux. When I query the database, the results only include the first letter of the ...
1
vote
1answer
15 views

Would the error log for MySQL for the IBM i be in mysql.general_log?

I have an IBM i running Zend DBI (Zend Supported MySQL 5.1) that I'm trying to find log information for. I do SELECT * FROM mysql.general_log and get 0 rows. my.cnf looks like this: [mysqld] ...
1
vote
1answer
72 views

Auditing DDL with MySQL

We have a MySQL database on an IBM i (as/400) that has many people with admin access. We would like to audit who is making changes, and when they are made. On SQL Server we would use DDL Triggers, but ...
1
vote
1answer
560 views

DB2 connection rejected with JDBC and ODBC

Good day to all, Currently I have 4 as400 servers, with db2 installed, is the production server and development server with its two replicas. The development java team uses IBM Toolbox for Java and ...
1
vote
1answer
480 views

SQL statement slower on as400 console than from remote odbc [closed]

I am having a strange problem on one of my i systems running 7.1 I am using jdedwards 8.0 as erp but the issue is not related as i just found out. After some research it came down to this. I run the ...
1
vote
3answers
1k views

Migrate DB2 UDB (Iseries) to SQL Server [closed]

I'm looking for a tools that allows migration of DB2/UDB databases to SQL Server. It's quite easy migrate schema and data using linked server or import export with SISS. But what about stored ...
1
vote
0answers
9 views

DB2 Iseries - dml change logs

Iseries system using I/90 Is there a way to check changes made to database object like tables and views from any systables or system views or any logs on db2 for iSeries machine.
1
vote
1answer
324 views

How to correctly use PFILE keyword in DDS Logical File? (AS400)

I'm having trouble wording this question right (also not sure if I'm in the right stack section) My issue is that I have a logical file ORDFILE located in the ORDRENTLB library, that is suppose have ...
1
vote
0answers
33 views

DB2 Change Journal Receiver Size

We are running DB2 on our iSeries, and there is a journal + journal receiver created. The current size of the journal receiver is 200MB (found using the DSPJRNRCVA command). Is there a way to change ...
1
vote
0answers
134 views

Auditing AS 400 DB2 library user access

As part of the testing to check for authorized users having access to applications and libraries on AS 400 systems, I need help to figure out a list of users having access to applications hosted on ...
1
vote
1answer
252 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
0
votes
1answer
2k views

Linked Server Between SQL Server and iSeries is rounding decimals

We just set up a new SQL Server 2008 database, and I got the link set up between our server and an iSeries As400 server that we get some of our data from. The problem I'm having is that it is ...
0
votes
2answers
930 views

Is there a SQL Server equivalent to “OVERRIDING USER VALUE”

While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries. Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to ...
0
votes
0answers
19 views

Macro to search list JDE ERP

I have a list of Part numbers in a txt file. I need to get the matching Inventory number for each from our JDE database. Is it possible to build a macro in I-Series to search a JDE database for ...
0
votes
1answer
117 views

SORT_SEQUENCE is wrong in as400 library indexes. Can this cause performance issues?

I have 4 AS400s running JDEdwards. Lately a performance issue occured as described here: AS400 Performace issue Although i am not an expert in AS400 , i tried to find out the cause. I think it came ...
0
votes
0answers
81 views

Issue with refactoring columns and how ODBC query receives data

We just installed iSeries 7 box and migrated a database from i5. The migration was to accommodate a new version of vendor's application. They made some changes to the columns by increasing them from ...
0
votes
0answers
30 views

Duplicate Customer Records for SQL Query from ODBC with Joins and Group Bys [duplicate]

Possible Duplicate: SQL Query with ODBC, Joins and Group Bys I am getting duplicate customer records with the query below. The main purpose of the query is to pull a list of customers into ...