SQL INSERT INTO SELECT Statement

Leave a comment

Create table2 on the fly using the structure of table1
SELECT * INTO table2  FROM table1

table2 must exists (created using the above statement)
INSERT INTO table2 SELECT * FROM table1

 

Advertisements

Useful Sybase IQ Scripts

Leave a comment

exec sp_iqconnection;exec sp_iqconnection;exec sp_iqcursorinfo;sp_iqstatus
DROP connection 178118sp_iqstatus;

 

select * from sys.sysindexwhere table_id = (select table_id from systable where UPPER(table_name) = UPPER(‘table_name’))and index_owner = ‘USER’
select * from sys.sysindexes where iname IN (‘EXTERNAL_POLICY_HG’, ‘PERIOD_END_PRD_ID_HG’)

 

SELECT DISTINCT t.table_name,  c.column_name FROM   systable t,         syscolumn c WHERE   t.table_id = c.table_id AND Upper (t.table_name) LIKE ‘%TIOCTP%’ AND Upper (c.column_name) LIKE ‘%HOSPITAL%’ORDER  BY 1,2

 

SELECT t.table_name,         i.index_name,         i.index_type,         i.index_owner FROM   sys.sysindex i,         systable t WHERE   i.table_id = t.table_id AND lower(t.table_name) IN ( ‘f_wf_activity’ ) ORDER BY t.table_name, i.index_name

SELECT * FROM   sys.sysindex WHERE  table_id = (SELECT table_id FROM   systable WHERE  lower(table_name) = ‘f_wf_activity’)

CM-XXX-1000 Error When Opening Report

Leave a comment

Problem(Abstract)

After deleting the temporary files on the BI server you may receive CM-XXX-1000 error message when trying to open a report in Report Studio.

Symptom

Error message that will appear is as follows:

CM-XXX-1000
NULL_MSG_CODE
An error occured while trying to add a new temp file.

 

Cause

The files and folders in the temp directory were deleted while the BI server was still running.

Diagnosing the problem

Review the contents of the temp folder (default location <cognos_install>\temp) and confirm if the directory is empty or not. If you are unsure as to the location of the temp folder consult the setting in Cognos Configuration.

Resolving the problem

Stopping and restarting the BI services will recreate the folders necessary for the BI server to operate correctly.

Rename Congos Burst files in Windows

Leave a comment

pushd \\ServerName\CognosTestFolder
call RemovePrefix.bat “Claim Summary – Burst-en-au-”   “*.pdf”
popd

 

::RemovePrefix.bat prefix fileMask
@echo off
setlocal
for %%A in (“%~1%~2”) do (
set “fname=%%~A”
call ren “%%fname%%” “%%fname:*%~1=%%”
)

Making query locks less restrictive

Leave a comment

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00938.1502/html/locking/X39708.htm

 

Making locks less restrictive

In contrast to holdlock, the noholdlock keyword prevents Adaptive Server from holding any shared locks acquired during the execution of the query, regardless of the transaction isolation level currently in effect.

noholdlock is useful in situations where transactions require a default isolation level of 2 or 3. If any queries in those transactions do not need to hold shared locks until the end of the transaction, you can improve concurrency by specifying noholdlock with those queries.

For example, if the transaction isolation level is set to 3, which normally causes a select query to hold locks until the end of the transaction, this command releases the locks when the scan moves off the page or row:

select balance from account noholdlock
    where acct_number < 100

If the session isolation level is 1, 2, or 3, and you want to perform dirty reads, you can use the at isolation level read uncommitted clause.

The shared keyword instructs Adaptive Server to use a shared lock (instead of an update lock) on a specified table or view in a cursor.

Not enough server threads available for this query

Leave a comment

 

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00170.1520/html/iqapgv1/iqapgv1723.htm

Insufficient threads

Possible cause

If the client receives a message like Not enough server threads available for this query [-1010011] ['QXA11'], the query requires more kernel threads for the IQ store.

Actions

  • Wait for another query to finish and release the threads it is using. Then resubmit your query.
  • Run the system stored procedure sp_iqconnection. The column IQThreads contains the number of IQ threads currently assigned to the connection. This column can help you determine which connections are using the most resources. Remember that some threads may be assigned but idle.

 

  • If the condition persists, you may need to restart the server and specify more IQ threads. Use the -iqmt server startup switch to increase the number of processing threads that Sybase IQ can use. The default is 60 threads per CPU for the first four CPUs and 50 threads per CPU for the remainder, plus threads needed for database connections and background tasks. For example, on a system with 12 CPUs and 10 connections: 60*4 + 50*(numCPUs - 4) + numConnections + 3 = 653. The minimum value is numConnections + 3. The total number of server threads cannot exceed 4096 on 64-bit platforms, or 2048 on 32-bit platforms. See “Starting the database server” in Chapter 1, “Running the Database Server,” in the Utility Guide.
  • If the server runs out of threads, or if sufficient threads are not available to a connection during a restore, Sybase IQ may return the error Ran out of threads. Start up server with more threads. (SQLCODE -1012024). The RESTORE command will try to allocate a “team” of threads for the restore operation. Sybase IQ will try to allocate at least one thread per backup device plus two threads per CPU, plus one thread to the “team” for the restore. Make sure that enough threads have been allocated on a per connection and per team basis as well as to the server. See “MAX_IQ_THREADS_PER_CONNECTION option”and “MAX_IQ_THREADS_PER_TEAM option” in “Database Options,” in Reference: Statements and Options.

Cognos Concepts: Stitch Query in Cognos Reporting Explained

Leave a comment

http://blogs.perficient.com/ibm/2012/05/17/cognos-concepts-stitch-query-in-cognos-reporting-explained/

 

Older Entries