Questions tagged [iseries]
IBM mid-range computers, previously called AS/400
58
questions
8
votes
2
answers
85k
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 ...
- 1,021
6
votes
7
answers
6k
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 (1,...
- 61
5
votes
1
answer
6k
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...
...
- 153
5
votes
1
answer
1k
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 ...
- 151
4
votes
2
answers
466
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 ...
- 83
4
votes
2
answers
5k
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 ...
- 936
4
votes
1
answer
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 ...
- 4,906
3
votes
3
answers
17k
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 ...
- 911
3
votes
5
answers
3k
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 ...
- 31
3
votes
1
answer
165
views
I need the identical lines to merge
Previous question: Return row with values if nothing fits the conditions
I almost got this working.
The problem that I have is that this variant of query does not merge the line from the SELECT with ...
- 33
3
votes
2
answers
9k
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 ...
- 911
3
votes
1
answer
2k
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?
- 479
3
votes
1
answer
3k
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 ...
- 2,687
3
votes
1
answer
2k
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 ...
- 131
2
votes
2
answers
2k
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,687
2
votes
3
answers
3k
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 ...
- 911
2
votes
2
answers
16k
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,
...
- 2,687
2
votes
1
answer
12k
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 ...
- 21
2
votes
2
answers
8k
views
How can I lock row for reading/updating in DB2?
I am trying to resolve a race condition in my application.
I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it ...
- 141
2
votes
1
answer
178
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 ...
- 41
2
votes
1
answer
656
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 procedure ...
- 21
2
votes
1
answer
764
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 ...
- 911
2
votes
2
answers
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 ...
- 203
2
votes
0
answers
723
views
Fixing data pivot/grouping with db2
I have a pretty straightforward sql query that I'm running on DB2 I-series 4 which is just performing some simple joins. The issue is that my materials table has it so that multiple material/color ...
- 121
1
vote
1
answer
5k
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 ...
- 911
1
vote
2
answers
6k
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 ...
- 203
1
vote
1
answer
227
views
EXECUTE stmt USING expression
I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, ...
- 113
1
vote
2
answers
5k
views
Select all data from IBM DB2 member table
Old as400 system has this thing called 'Members'. Basically you can have multiple members on a table and then work with only selected one.
More Info
When you select from a table that has members you ...
- 141
1
vote
2
answers
83
views
How we can design best optimized solution: Generate huge random numbers and check in database table whether this numbers are there or not
We have one table like:
Table Name:
random_number_series
Its columns are as below:
number [numeric(10)]
name [varchar(10)]
status [tinyint(1)]
type [tinyint(1)]
...
- 107
1
vote
1
answer
100
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]
sql-...
- 2,687
1
vote
1
answer
1k
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 ...
- 2,687
1
vote
1
answer
3k
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 ...
- 125
1
vote
1
answer
3k
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 ...
- 111
1
vote
1
answer
1k
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 ...
- 121
1
vote
3
answers
2k
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 ...
- 113
1
vote
0
answers
1k
views
IBM db2 FOR XML equivalent
I've been trying to Query an item with its sublist all in one go and one way is to use for xml and subquery. I've been using this strategy with sql server and works fine.
The relationship is Order ...
- 43
1
vote
1
answer
2k
views
Cannot drop primary key or add primary key
This db2 query:
alter table mylib.mytable add primary key(field1, field2, field3)
returns
[SQL0624] Table MYTABLE in MYLIB already has a primary key'.
However
alter table mylib.mytable drop ...
- 65
1
vote
0
answers
53
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.
- 11
1
vote
0
answers
122
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 ...
- 11
1
vote
1
answer
671
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 ...
- 11
0
votes
1
answer
238
views
Using a DB2 merge with conditionals for matches?
Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work.
The logic I'm looking for is this:
If there's a match AND the ...
- 103
0
votes
1
answer
2k
views
SQL Server - Query Over Linked Server to IBM DB2 throws an error
We have a partner that uses a IBM DB2 iAccess database, which we connect to using a linked server on our SQL Server.
It's been working great for a long time, but over the weekend they upgraded their ...
- 203
0
votes
2
answers
3k
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 ...
- 1,045
0
votes
1
answer
26
views
why does the order of fields return 0 rows, but move the field does
I have two queries that return the same data, only the calculating field with the case is moved. I am using the Access Client Solutions "run query" to execute the query.
If I run this one
...
- 1
0
votes
1
answer
78
views
iSeries journaling performance
I am responsible for a legacy application deployed on an iSeries V7R3. The application is fairly high volume, 2 million or so transactions per month.
The database for this app spans multiple libraries ...
- 103
0
votes
2
answers
63
views
Return row with values if nothing fits the conditions
I have his query that works fine, but I need it to return a line when nothing is found to suit the conditions:
SELECT D.CODPRO, D.VALPRO, COALESCE(SUM(d.UVCLIV), 0) AS SUM
FROM FGE50LM0TV.GESUPE E ...
- 33
0
votes
1
answer
397
views
Create View giving SQL7029
If relevant, I'm using IBM i V7R1M0.
I have some code in C#:
var generateSqlSql = $@"
CALL QSYS2.GENERATE_SQL(
'{obj.Name}',
'{obj.Library}',
'VIEW',
CREATE_OR_REPLACE_OPTION => '...
- 279
0
votes
1
answer
2k
views
DB2 select from final table update gives SQL0199 error
Goal: I am trying to retrieve records that were affected by UPDATE query.
Based on IBMs article I can use the following query:
SELECT empno, salary FROM FINAL TABLE
(UPDATE employee SET salary = ...
- 141
0
votes
1
answer
57
views
DB2 AS400, Hammer DB setup issue
I am working with Hammer DB to connect to remote DB2 AS400. However, I am running into issue in build schema.
[IBM][CLI Driver][AS] SQL0204N "QSYS.ANAND" is an undefined name. SQLSTATE=...
- 3
0
votes
0
answers
94
views
My linked server from as400 disconnects every time SQL Server is restarted
Every time I restart SQL Server, my linked server which is linked with AS400 gets disconnected.
Every time it gets disconnected, I simply refresh the provider and I'm good to go. But, sometimes if I'm ...
siddharth rao