Tag Info

Hot answers tagged

6

I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key: UPDATE ( SELECT t1.a AS t1a, t2.a AS t2a FROM t1 INNER JOIN t2 ON t2.b = t1.b ) u SET t1a = t2a ; Tested at (Oracle 11g) SQL-Fiddle Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the ...


5

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement. This will create your table in QTEMP regardless of whether one already exists or not.


5

I've just come across StandardCDC which might be of interest: StandardCDC captures data manipulation language (DML) changes for a specified table and stores the results in a relational format. The capture table mirrors the column list of the tracked object, with options for storing only specific columns.


5

You have a working query, but you are selecting: FROM CF30, EC01, OC02, OM01U1, RS2101F With no explicit joins and only one implicit join: WHERE OM01U1.OM01015 = RS2101F.OUTNUM This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say: FROM OM01U1 INNER JOIN RS2101F ON OM01U1.OM01015 = ...


5

When you use openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076


4

Triggers and audit/history tables. And columns in the main table for Inserted/Updated A history table is easily queried and can be demonstrated to an auditor Universal across different RDBMS No history write = no write because the trigger is part of the transaction From the audit/history tables you can get the state of the database at any point in time. ...


4

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax. Example: MERGE dbo.t1 AS Target USING (SELECT a,b,c FROM dbo.t2) AS Source ON (Target.b = Source.b) WHEN MATCHED THEN UPDATE SET Target.a = Source.a


3

Change tracking can be used in Standard Edition. It will allow you to track changes to the data, but won't be able to tell you who made them. Your options are basically the following: triggers - maybe even use instead of triggers - for an update you can move the current copy of the row to a history table, and replace it with the active copy. move to ...


3

You are not checking for an end of cursor condition. DECLARE END_CL INT DEFAULT 0; DECLARE CL CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1; FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , ...


3

This should probably be a comment rather than an answer since I'm not sure if my reasoning is correct, but then I wouldn't be able to have any formatting. Could you create a temporary table and JOIN instead? ...<sql statements and joins> JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.b Edit: if the values you're checking ...


3

You can use a windowing function to partition the data. If I knew your data better I'd rewrite it without a subselect, but the wall of text is making my head hurt ;) Here you go: SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY OM01U1.OM01015 ORDER BY EC01_LASMTCDAT DESC) as r, OM01U1.OM01041 as Loc, OM01U1.OM01015 AS Outlet, -- SNIP -- -- SNIP -- ...


3

Other options except the ones wrote by Aaron and gbn: use a 3rd party audit tool (redgate, lumigent...) implement custom package using fn_dblog system function (reads the transaction log) use 24/7 server traces (already mentioned by Aaron, sorry)


2

Actually, you question has less to do with "DB2" than it does with one of IBM's main products - IBM i on Power (also known as System i, iSeries, and AS/400 in the past). For whatever reason, IBM chooses to refer to a lot of things as "Objects" within their system. This gets very confusing to hear, because in and of itself, the operating system i5/OS is not ...


2

You are joining 5 files, but have only one condition that limits one of those joins. WHERE OM01U1.OM01015 = RS2101F.OUTNUM This condition should go into your join specs FROM OM01U1 JOIN RS2101F ON OM01U1.OM01015 = RS2101F.OUTNUM JOIN CF30 ON _____ = ______ JOIN EC01 ON _____ = ______ JOIN OC02 ON _____ = ______ If some of these ...


2

In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well: CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()); GO CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER ...


2

I've tested two tools that can provide info about transactions including when, who, using which computer and application ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option It has 2 built in reports, and the good thing about it is that it saves all captured ...


2

One thing that you are missing in your stored procedure declaration is the DYNAMIC RESULT SETS option. The default value for that option is 0, may be that's what confuses the client software. Whether it (the client software) can even handle cursors returned by a stored procedure is another story.


2

The solution to reporting on iSeries SQL stored procedure in SSRS is to install DB2UDB .Net provider that comes with IBM Client Access into BIDS by adding two XML tags to RSReportDesigner.config. Add the following to <Data> tag in .config: <Extension Name="DB2UDB" Type="IBM.Data.DB2.iSeries.iDB2Connection,IBM.Data.DB2.iSeries, Version=12.0.0.0, ...


1

The problem was something simple yet hard to find. The indexes' sort sequence was wrong. The clients were wrongly configured to use language preference for sort sequence. So when the indexes were created they had language specification for sorting. The server when a job was submitted, was looking for indexes with *HEX specification so he was ignoring ...


1

Check the DTC and SQL Browser service is running under right service account. also check this following MS-BOL links for more configuration help. Recommended MSDTC settings for using Distributed Transactions in SQL Server you can use this tool to identify if your system has proper MSDTC configuration. 2012 Best Practices Analyzer Last but not least ...


1

Start with this statement before trying to execute the function: SET PATH TESTDAT;


1

iSeries Navigator did not "return" two statements. It echoed them in the messages tab, NOT the data results tab. SYSIBM.SYSDUMMY1 is an IBM supplied table with 1 column and one row. You got the correct answer for the procedure you wrote. SYSDUMMY1 is generally used in example code as a proxy to evaluate expressions that do not require any table. For ...


1

Using System i Navigator, you can run your queries in there to see if there are more indexes that may be recommended. The system tries it best to create the right indexes before running and caches those access paths. One thing I did in my last project with some larger queries was to create procedures. Then the system stores those access paths and speeds up ...


1

As Heinz Z. did, I discovered the problem. One of your function parameters is char, while you pass the string literal 'ANY', which is considered a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it. Solution is either changing function parameter to varchar or cast parameter to char in ...


1

Figured it out!! Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across.


1

Don't know which version of i5/OS you are on. This is from V5R4 documentation. But things you may be coming across: 1 - The total number of subselects in a fullselect (UNION or UNION ALL clause) is greater than 32. 2 - The total number of columns, constants, and operators is greater than the SQL limits. 3 - The sum of the lengths of the non-LOB columns in a ...


1

This is not an easy task to translate from one flavor of SQL to another. That's either (kind of) expensive or difficult to do for yourself. No database language flavor is really easy to digest and translate into another, because all have lots of system procedures, data types, functions and others that are really system specific. Each SQL flavor implements ...


1

When you are overwhelmed with a long complex query, involving so many pieces, it is often a good strategy to break it down into smaller parts, get them working, then build it back up, in small stages. Focus first on a "mind-sized bite". Don't bite off more than you can chew in a single mouthful. Start simple, and add things back in layers. You probably ...


1

Here is documentation for DB2 for i on i5/OS V6 R1. Here is specific to DB2 itself. Here is specific to DB2 SQL.


1

SQL2008 has a new feature called CHANGETABLE that you can use. Check this link to read more about it. CHANGETABLE returns change tracking information for a table. You can use this statement to return all changes for a table or change tracking information for a specific row.



Only top voted, non community-wiki answers of a minimum length are eligible