texts: Database Design Cars-Models / Car Classifieds

texts:
I'm developing a database for Car Classifieds. I'm not sure how is the best way to design it. There are several features of cars like if it has 2 or 4 doors, if it is 4x4 or 4x2, engine capacity of 1.6 or 2.0.

All these features are particular for a car but they have to be limited to what that model has. So if someone is posting a bike, for example, it would not show the option in a drop-down for 4x4, or engines other than that model's.

I don't want to make this generic. It will only be for cars, so thinking about how it would be best for generic classifieds is not necessary.

What would be the best way to design this?

More Info:

My problem is how to design the relation between a Car Model and an actual car. For example, the Year of a Model could go from 1990-2000. When someone is going to add a car of that model I only want them to be able to choose from the options that model has. How do I store this in a database?


texts: Oracle DB administrator-specific security features

texts:
Yesterday I was presenting an internet banking solution to a potential client management. My offer is based on using MS SQL Server 2008 RDBMS.

One of the client's officers mentioned, that Oracle's RDBMS has some "specific" features that allow a security department to ensure that no sensitive data is visible to an admin, that MS SQL doesn't have.

For me it is hard to imagine how can this be mitigated, unless preventing access to some tables in a DB.

My question - is there really a "know-how" in Oracle RDBM when it comes to security tuning, that MS SQL does not implement?


texts: How to optimise MySQL query that uses dependent subquery

texts:
I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score.

There are only 1000 rows in the image table and about 40,000 rows in the seen table.

The current query is taking 7 seconds or more on a small EC2 LEMP stack instance.

SELECT
    images.id AS image_id,
    images.created_at AS time_ago,
    pictures.name AS picture_name,
    pictures.location AS picture_location,
    images.like_count AS like_count,
    images.seen_count AS seen_count,
    images.like_count /
    POWER(
        (HOUR(TIMEDIFF(NOW(), images.created_at))+2), 0.5
    ) AS s
FROM
    images
LEFT JOIN
    pictures ON images.picture_id = pictures.id
WHERE
    images.id NOT IN (SELECT
        image_id
    FROM
        seen
    WHERE
        user_id = $user_id)
AND images.id NOT IN ('$exclude_images_string')
AND images.user_id != $user_id
AND images.like_count >= 10
AND images.moderated IS TRUE
ORDER BY s DESC
LIMIT 30

How could this be optimised to run faster?

Edit: Here are the create table statements for images and seen:

    CREATE TABLE images (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  picture_id int(11) NOT NULL,
  user_id int(11) NOT NULL,
  moderated tinyint(1) DEFAULT NULL,
  seen_count int(11) NOT NULL DEFAULT '0',
  like_count int(11) NOT NULL DEFAULT '0',
  created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id,picture_id,user_id),
  KEY USER (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=1246 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE seen (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  image_id int(11) NOT NULL,
  user_id int(11) NOT NULL,
  created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id,image_id,user_id)
) ENGINE=InnoDB AUTO_INCREMENT=39107 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts:
I am currently trying to migrate my ssms registered servers from one machine to another.  This is complicated by the fact that I am running ssms 17 on the source, and ssms 16 on the destination.  I would run 17 on the second computer but it is only a 32 bit machine, so i am sol for that.

I have well over 100 machines saved so I am hoping there is some way to move the configuration rather then have to start building it from scratch.


texts: Trouble with surname "Null" and LOAD DATA INFILE

texts:
I'm given source data in a tab-delimited text file where the string columns are not quoted.

Among the columns is LAST_NAME, and naturally my source data contains a person with the surname Null.  Further, some of the records have redacted names, which I would like to store as NULLs, so I can't define my column as NOT NULL.  (A sentinel value is used for the redacted names, so I can identify them.)

I am trying to import this data via LOAD DATA INFILE.

I read each source column into a variable and pass those variables through a function that looks for the sentinel values and translates them to NULL.  When this function returns a NULL value, the column gets assigned as NULL.  But when this function returns the string 'NULL', the column still gets assigned as NULL, instead of being assigned the string 'NULL'.

How can I prevent the string 'NULL' from being interpreted as a NULL value in the context of a SET clause of a LOAD DATA INFILE command?  (Assigning to a nullable varchar column.)

Update with specific data and commands:

An input data file (/tmp/data.txt); note that the fields are TAB-delimited, but I don't know how to do that here:

    SMITH   Other text is
    REDACTED        "inconsistently quoted"
    NULL    REDACTED

My commands:

    CREATE FUNCTION despair(value varchar(255))
      RETURNS varchar(255) DETERMINISTIC
      RETURN
        CASE value
        WHEN  NULL  THEN 'NULL'
        WHEN 'NULL' THEN 'NULL'
        WHEN 'REDACTED' THEN NULL
        ELSE value
        END;

    CREATE TABLE `misery` (LAST_NAME varchar(50), TEXT varchar(100));

    LOAD DATA INFILE '/tmp/data.txt' INTO TABLE `misery`
      FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n'
      (@name, @text) SET LAST_NAME = despair(@name), TEXT = despair(@text);

    SELECT LAST_NAME, TEXT, LENGTH(LAST_NAME), LENGTH(TEXT) FROM `misery`;

Current output.  Note that the TEXT column looks right, because quotation marks are removed as desired, but the last row's LAST_NAME is a NULL value, but should instead be the string 'NULL'.

    +-----------+-----------------------+-------------------+--------------+
    | LAST_NAME | TEXT                  | LENGTH(LAST_NAME) | LENGTH(TEXT) |
    +-----------+-----------------------+-------------------+--------------+
    | SMITH     | Other text is         |                 5 |           13 |
    | NULL      | inconsistently quoted |              NULL |           21 |
    | NULL      | NULL                  |              NULL |         NULL |
    +-----------+-----------------------+-------------------+--------------+
    3 rows in set (0.00 sec)


texts:
I have a table in a MySQL database for which innodb_file_per_table is enabled. One of my tables is about 20GB and growing. How can I partition the table into separate files?

Suppose that I have a table named tab1 that is stored on /var/lib/mysql/db_name/tab1.frm and the size is about 20GB. I want to partition that table to split into 2 files tab1-1.frm and tab1-2.frm each one sized at 10GB.

Table definition:

CREATE TABLE cdr (
     gid bigint(20) NOT NULL AUTO_INCREMENT,
     id bigint(20) NOT NULL,
     start datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
     clid varchar(80) NOT NULL DEFAULT '',
     src varchar(80) NOT NULL DEFAULT '',
     channel varchar(80) NOT NULL DEFAULT '',
     duration int(11) NOT NULL DEFAULT '0',
     uniqueid varchar(32) NOT NULL DEFAULT '',
     dnid varchar(20) NOT NULL DEFAULT '',
     service varchar(100) NOT NULL DEFAULT '',
     cost int(11) NOT NULL DEFAULT '0',
     PRIMARY KEY (gid),
     UNIQUE KEY id (id,prefix),
     KEY start (start),
     KEY clid (clid),
     KEY service (service)
) ENGINE=InnoDB AUTO_INCREMENT=37024605 DEFAULT CHARSET=latin1


texts: How to list all constraints of a table in PostgreSQL by schema?

texts: Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.


texts: How do read queries against Postgres's JSONB type compare to Couchbase?

texts:
I'm working on a small project which would involve running queries against deeply-nested (up to 5 layers) keys in dictionaries. This would be specifically a read-heavy environment, with only one large set of rites during the app's bootstrapping (nominally once per install). The two options I'm considering for use as database backends right now are PostgreSQL (10.0) and Couchbase (5.0.1).

I'm reasonably familiar with Postgres and its JSONB facilities in terms of performance (although not implementation details), but I'm not familiar enough with Couchbase to draw a concrete comparison, and it's difficult to find this specific comparison in any online benchmarks.

Ignoring issues about the fact that Postgres isn't a document store, could Postgres consistently outperform Couchbase in read queries involving accessing a deeply-nested key/value pair?


texts: Aggregation by positive and negative values


texts: Performance issues of `fetch first n rows only`

texts:
Consider a query of the form

insert into new_table (new_column)
select column as new_column
from table
where column like '%pattern'
fetch first 1000 rows only;

table has a sufficient number of entries such that at least 1000 ones satisfy the criterion. What irritates me is that with the criterion commented out, the query runs much more slower (using DB2) than with the criterion enabled. I would have expected it to run faster with less restrictions since less rows would have had to be considered. Where am I mistaken?


texts: I'm a MS SQL DB Developer looking to get more permissions on the server

texts:
So I am trying to use Visual studio and I can't import the DEV database into a project. I run into this issue a couple times a week since I am either creating SSIS Packages, or building a database from scratch, or creating SSAS models that require me to hit many databases, create job schedules....

My current error is that I can't reverse engineer the DEV database in Visual Studio because I don't have view definition. This is probably one of the more frequent issues I run into. I had the same error when I tried to see an Execution plan.

So based on this article http://sqlmag.com/database-security/security-options-developers-devsandbox-environments
I am wondering if Grant Control on Schema will cover everything I need.

Another issue that comes up is that I can't query the database to see who might be locking my query. Running SP_who2 only tells me what I am doing. It is quite frustrating to have to turn in a ticket and wait for a DBA to figure out the issue. What right do I need to have to run SP_WHO2 or run a query to see what is hitting the system.

And should I not be able to kill my own SPID when something goes awry? The stop button in SSMS does not always work.

I'd also like to run Brent Ozar's spBlitz tools so I can tune my own queries. Should that not be a developer's task. I'd like to know if I create an index and it is ever hit and how often. Or should I include a column I need in another covering index.

Thoughts? Will Grant Control give me everything I need as a Database developer, not to be confused with a report writer, or someone writing basic procs for an app.


texts: Is it possible to create a cube without measures in Schema Workbench?

texts: Starting from a star schema without measures, is it possible to create a cube without measures in Schema Workbench?


texts: Extract new lines from log file to MYSQL

texts:
everyone.
I am new here, I was recommended by Onare, welp, this will be my first question.
I have a file for ex. "file.log" that contains printing logs.

LINE1: PRINTERNAME | userwhichprinted | ID | DATE | 1 | 1 | - | FROMIP | FILENAME | - | -
LINE2: PRINTERNAME | userwhichprinted | ID | DATE | 1 | 1 | - | FROMIP | FILENAME | - | -
LINE3: PRINTERNAME | userwhichprinted | ID | DATE | 1 | 1 | - | FROMIP | FILENAME | - | -

And so on, actually it is a CUPS Server, someone may be familiarized with it.
I want to make a trigger that put every new line to the database. If there are any more questions needed, let me know, thank you in advance.


texts:
I have the following two tables
customers

| customer_id | firstname | lastname | email                |
|-------------|-----------|----------|----------------------|
| 1001        | John      | Doe      | johndoe@gmail.com    |
| 1002        | Lorem     | Ipsum    | loremipsum@gmail.com |
| 1003        | Alph      | Beta     | alphabeta@gmail.com  |

And orders

| order_id | customer_id | via | status     |
|----------|-------------|-----|------------|
| 100      | 1001        | WEB | pending    |
| 101      | 1001        |     | pending    |
| 102      | 1001        | POS | pending    |
| 103      | 1002        | WEB | processing |
| 104      | 1002        | APP | pending    |
| 105      | 1001        | APP | completed  |
| 106      | 1003        |     | pending    |

I want to get the following resultset:

All the customers who have ordered via WEB or (EMPTY) but not from POS
All the customers who have ordered only via APP


texts: Error calling stored procedure from trigger in Oracle Database 11g

texts:
I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated.  I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to know what I'm doing wrong.

I've created the following Java source:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED TryJavaHelperSource AS
import java.io.*;
public class TryJavaHelper {
    public static void Run(String arg1, String arg2) throws Exception
    {
        // …
        return;
    }
}
/

And I created a stored procedure that calls this Java source:

CREATE OR REPLACE PROCEDURE TryJavaHelperRun (arg1 IN VARCHAR2, arg2 IN VARCHAR2)
AS LANGUAGE JAVA NAME 'TryJavaHelper.Run (java.lang.String, java.lang.String)';
/

I'm trying to create a trigger on this table:

create table TryTable (
    arg1 varchar2(20),
    arg2 varchar2(100)
);

I get an compile error when I attempt to create the following trigger:

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    CALL TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Here's the error I'm getting:

SQL> sho err
Errors for TRIGGER TRYTABLE_BEF_UPD_ROW:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/6      PLS-00103: Encountered the symbol "TRYJAVAHELPERRUN" when
         expecting one of the following:
         := . ( @ % ;
         The symbol ":=" was substituted for "TRYJAVAHELPERRUN" to
         continue.

Why is this error occurring, and how do I fix it?

UPDATE:

It looks like it works when I take out the CALL statement.  What are the rules governing when to use CALL and when to just type the name of the function/procedure?


texts: possible corruption or other issue that doesnt show on basic scans?

texts: I have a WP table with about 33000 records that suddenly is taking more than a minute to query. According to optimization tools, everything is good but given the sudden nature of the trouble, I doubt that. We went from those tables taking way less than second to query to between 48-93 seconds now each time. Have confirmed no other load on the server slowing it down so its all down to this. Looking for other places to look or check given the issues?


texts: What is the PREEMPTIVE_REENLIST wait type in Microsoft SQL Server 2008 R2

texts:
This is not urgent, just curiosity.

QUESTION:

I was trying to troubleshoot a slow server today, and I came across a high figure for a wait type called PREEMPTIVE_REENLIST. I'm not able to find this documented anywhere.
TOADWORLD seems to at least know it exists, but doesn't have any actual info.
Nothing on MSDN search or Stack Exchange search.
Nothing on Google except the TOADWORLD links.

Was just wondering if anyone here had encountered it and knew what it was all about.
Thanks.

PLATFORM:

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012
  16:41:53   Copyright (c) Microsoft Corporation  Standard Edition
  (64-bit)  on  Windows NT 6.1  (Build 7601: Service Pack 1)
  (Hypervisor) (Windows Server 2008 R2 virtualized on VMWare)


texts: Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

texts:
I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:

SELECT COALESCE(c.FirstName
                ,(SELECT TOP 1 b.FirstName
                  FROM TableA a
                  JOIN TableB b ON .....)
                )

If, for example, c.FirstName = 'John', would SQL Server still run the sub-query?

I know with the VB.NET IIF() function, if the second argument is True, the code still reads the third argument (even though it won't be used).


texts: How do I query a JSON array stored in a JSON object?

texts:
I have a column with name 'date' of type json.

And need to return all records that contain the "ID" equal "10"

Below is the json structure of the column 'date'

{
  "Name": "ABC",
  "ID": [
    "10",
    "20",
    "30"
  ]
}

How will the query return what I need?


texts: Impact of Log backups restore process after file\file group restore on a database

texts:
When we restore file\file group(backup1) backup on a database is it necessary to restore all log backups that were taken after backup1 to get database in consistent state??
and also what will be the impact of log restore process on database state and those files?


texts:
MySQL has feature of logging all sql queries - General log.

But this log does not contain queries durations.

How to log all sql queries to analyze those durations?


texts: Why postgresql pg_dump exports a view as a table?

texts:
I am using PostgreSQL 9.3 pg_dump tool for extracting only the public schema definition using:

pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql

but when I check schema.sql one of our views appears in a CREATE TABLE statement instead of a CREATE VIEW statement.

But, if I pg_dump the specific view using:

pg_dump -s -t myview -h host -U postgres --dbname=db > ./schema.sql

then schema.sql contains the actual view definition.

So, why is this happening? Thank you guys!


texts: Data compression at distribution level in SQL Server replication

texts:
I have SQL Server transactional replication setup.

For one of the server I am facing slowness due to limited network bandwidth. Is there any way to compress data at distribution level only.


texts:
I am currently working on bigquery(standard sql), where I have a timestamp field which I want to convert to date and time in 2 separate columns. I tried doing this select EXTRACT(DATE FROM timestamp) from table .

I tried casting timestamp to date as well, but it is giving me an error. Any suggestions would be really appreciated

Input

timestamp
1539167364779001
1542242226310005
1544881556726002

Expected output

date         time
10/10/2018   3:59:24 PM
11/15/2018   6:07:06 AM
12/15/2018   7:15:56 PM


texts: Physical co-clustering of two or more logical relations/tables in modern RDBMS

texts:
One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join can be performed much faster.

I've googled "co-clustering" and surprisingly few results showed up. I found nothing on MySQL for example. There is some indication that Oracle offered it 10 years ago. Is co-clustering still a valid option for optimization?

For example, you have two relations/tables:

Employee (id, name, age, did)
Department (did, location)

A typical query for which you optimize maybe looks something like this:

SELECT E.name,
       E.age
  FROM Employee E,
       Department D
 WHERE E.age = 25
   AND E.did = D.did;

If you had 1,000,000 employees and they are all between 25 and 27, the best join method is probably sort-merge join or hash join - both require multiple scans.

Now, if you store tuples/rows of multiple relations/tables on the same page, you can use a physical structure that stores a department with a certain did together with employees with the same did. Notice that such join requires much less IOs.


texts: How do I set permissions for Package Body

texts:
Here's my situation (slightly envaguened to protect the innocent): I have a package on a Test system and a package on Prod system that need to have identical permissions to access exiting packages and functions. Both systems are Oracle. I use the following query to check existing permissions:

with objects as (
  select owner, table_name, listagg(privilege, ', ') WITHIN GROUP (ORDER BYPRIVILEGE) AS PRIVS
    from all_tab_privs_recd
   where grantee = 'EABINTEG'
   group BY owner, table_name
   order by owner, table_name)

select ob.owner as OWNER, ob.table_name as NAME, ao.object_type as TYPE, ob.PRIVS as GRANTS
  from objects ob left join all_objects ao on ob.owner = ao.owner
                                          and ob.table_name = ao.object_name
  order by ao.object_type, ob.owner, ob.table_name;

The queries come back almost identical, except Test has some rows that say the account has EXECUTE permissions granted for the PACKAGE BODY. The administrator has run queries to grant my account execute on the packages, I can see identical EXECUTE permissions granted for the PACKAGE on both Test and Prod.

I've looked around and haven't found anything. Has anyone else run up against this? How do I grant EXECUTE privileges for a PACKAGE BODY. Is this a SYNONYM issue? Help! Thanks!


texts:
my UNION queries cannot work when tables created by WITH are used, is this a SQLite limitation? for example -

This works:

with
T_one as (
    select * from T
),
T_two as (
    select * from T
)
select * from T_one

This does not work:

--union cannot see tables made by with
with
T_one as (
    select * from T
),
T_two as (
    select * from T
)
select * from T_one union select * from T_two

  (sqlite3.OperationalError) no such table: T_one [SQL: u'--union cannot see tables made by with\nwith\nT_one as (\n    select * from T\n),\nT_two as (\n    select * from T\n)\nselect * from T_one union select * from T_two']


texts: Temporary replication of data between continental datacenters

texts:
I need a way to temporarily replicate data between two data centers on two continents for a small number of large tables in Oracle. The data in the tables is around 30Gb but the transfer rate is only around 5Gb/hour.

I guess that the data does not change greatly on a day to day basis.

What options do we have for replicating the data that are simple and cost effective for this short term requirement?

Clarifications

Temporarily i.e. two months
A subset of the database, 2 or three large tables
One way, until the database is migrated to the new datacenter


texts: Does a data type affect performance in a view?

texts:
I just noticed that in our of our databases we have a view that converts a BIT column to an INT. The underlying table has a computed BIT column which is defined like this:

IncludeInJobTotals as (CONVERT(bit,
case
when FL.[Status]=(5)
  or FL.[Status]=(3)
  or FL.[Status]=(2)
then (1)
else (0)
end,(0)))

In a view that uses IncludeInJobTotals we are casting it as an INT. Are there any performance problems that this can cause? What other issues might there be? Are there any reasons to use INT over BIT?


texts: Process dimension task is querying the wrong column!

texts:
I'm in the process of building a new data warehouse and I am starting out by building a few dimensions.  I have created a fairly typical "Date" dimension and that seems ok.  Now I am trying to create a "Person" dimension which contains "Date of Birth Key" and "Date of Death Key" attributes, both of which are related to the key attribute of the Date dimension.

When I process the Person dimension I am receiving an error about duplicate keys.  Looking into it, I found that the code that is generated for processing the Date of Birth attribute is incorrectly joining to the Date dimension using the Date of Death column:

SELECT   DISTINCT
         [Dim_Person].[Date of birth key]
         ,[Dim_Date].[FullDate]
FROM     [Dim].[Person] AS [Dim_Person],
         [Dim].[Date] AS [Dim_Date]
WHERE
   [Dim_Person].[Date of death key] = [Dim_Date].[DateKey]  <--- This line is WRONG!

I have attached a screenshot showing the Date of Birth attribute.  The Date of Death attribute is identical other than looking at the Date of Death Key field.

Am I doing something wrong, or is this a bug in SSAS ?


texts: Query to get all schemas in a database in PostgreSQL?

texts: I'm not looking for the command \dn of psql, but instead of a query in SQL language that selects all schemas in a given database.


texts: Who is "focker" and why does he own my schema?

texts:
I have a Postgres DB deployment in Compose.io, and I am using the standard "compose" db for my app.  I would like to wipe out the current DB contents so that I can recreate the DB with a new schema.  I have read that the best way to do this is with "drop schema public cascade", but when I try to do this on my db I get

ERROR:  must be owner of schema public

It turns out that the schema is not owned by admin (the user that compose provides for psql access), but by a user named focker.

compose=> \dn
 List of schemas
  Name  | Owner
--------+--------
 public | focker
(1 row)

I tried changing the schema owner but that fails with the same error as above.

Why is my db set up this way?  Is there any way I can change it so that the admin user can wipe out a db?


texts:
I have a table 'class' owned by the schema 'test' and I need to audit all operations on the particular table.

audit INSERT,UPDATE,DELETE ON test.class --  will this be sufficient to do the trick?

Should I enable any other kind of Fine grained auditing to capture all the details.

Oracle12c/RHEL - I have set audit trail to DB. And sys operations to true.

Inputs needed here


texts: Movement of data from one data file to another within PRIMARY Filegroup

texts:
Scneario: We had only 1 data file(.mdf) which is now around 900 GB. We are planning to split it across multiple drives to improve IO performance.

As of now we have created 8 different data files in 8 drives within PRIMARY Filegroup.

what is the most efficient of moving data from 1 data file(.mdf) to another within same filegroup i.e PRIMARY Filegroup.

If that is not possible, then what is the best way of splitting the data across multiple drives in the same database?

We tried using DBCC SHRINKFILE with EMPTYFILE but it throws errors as it cannot move system files from .mdf file.


texts: How to copy tablespace from one database to another database in Oracle 11g

texts: How to copy tablespace and datafiles from one database to another database in Oracle 11g in same server ?


texts: Solution to error NID-00135 in Oracle 10g, Solaris 10

texts:
We are trying to build a test version of a production database by restoring the production backup using RMAN and renaming the database to test.

We've successfully restored and opened the database as production (with different IP from the real production server).

When using the NID utility to rename the database to test, we are encountering this error:

NID-00135: There are 1 active threads.

We've been having difficulty getting the database to shutdown normally using the SHUTDOWN IMMEDIATE command.  After an hour or so of waiting for the damn thing to die, we are forced to resort to grepping all the ora_* processes and killing them.

We left the listener up, the SQLPlus thread running, the bash thread owned by the Oracle user running, but that's about it for all Oracle-owned processes.

We've even restarted the server.

Are we missing something here?


texts: Repeating rows of date intervals Between dates Firebird

texts:
I am stuck in a case that on my financial sys, I need to add a option for value to be monthly, weekly or every 2 weeks

example:

DESC    |   VALUE |    DATEADD |  FREQUENCY
--------+---------+------------+-----------
SALARY  | 1000,00 | 26/03/2017 |         30 (MONTHLY)
DINNER  |   50,00 | 26/03/2017 |          7 (WEEKLY)
BUSTICK |   75,00 | 26/03/2017 |         15

select
    *
from
    tableexpensives
where
    datesys between '01/01/2017' and '31/12/2017'

so it should show on sql

APR | SALARY   | 1000,00 | (1x month)
APR | DINNER   |  200,00 | (4x month)
APR | BUSTICK  |  150,00 | (2X month)
MAI | SALARY   | 1000,00 | (1x month)
MAI | DINNER   |  200,00 | (4x month)
MAI | BUSTICK  |  150,00 | (2X month)
....

Any idea how to do that?


texts: Is database performance optimization independent of the size of a database or a table?

texts:
If by rewriting an operation or a piece of code the performance has increased by 30% in a program reading and writing to a table with 100,000 records, does that optimization remains same if the table grows and would have 10,000,000 records i.e. still 30%?

Given that the hardware has not been modified.


texts: The server principal "loginname" is not able to access the database "SSISDB" under the current security context

texts:
We have a stored procedure which calls an SSIS package from the SSISdb database.

It works when run as myself using my domain account, however the VB6 application which will call this connects to the databases via a SQL account.

SSIS needs to be run via a windows authentication account, so to get around changing the code to connect as a windows account, which is a big task apparently.. within the stored procedure that calls the ssis package, I added the "WITH execute as 'domain login'".

When you then run the stored procedure it comes up with the error:

  Msg 916, Level 14, State 1, Procedure "SP", Line 17 [Batch Start Line
  10] The server principal "windows account" is not able to access the
  database "SSISDB" under the current security context.

It has all of the correct permissions to the SSISDB, it has been granted ssisadmin under the msdb database.

I have also checked that its not an orphaned user against the databases - that's all fine.

I even made the login sysadmin briefly to test that - and the same error still appears which is strange.

I confirm that the SSIS package does not access any file shares or anything like xp_cmdshell.

It works if I log in as the domain account that's in the EXECUTE AS, so it seems it doesn't like the EXECUTE AS statement.

Any ideas what is going on here?


texts: How to determine the fully qualified tablenames of aliased tables, when a specifically named column is used in a stored procedure in SQL Server?

texts:
suppose StoredProcedure1 (SP1) contains the following
SELECT
      A.COL1
     ,A.COL2
     ,B.COL2
     ,C.COL5
    FROM MYBIGTABLE A
    INNER JOIN MYSMALLTABLE B ON A.IDCOL  = B.IDCOL
    INNER JOIN MYOTHERTABLE C ON B.ID2COL = C.IDCOL

Suppose I want to know about all such stored procedures that contain columns called COL2
I realise that I can query the stored procedure definitions in metadata tables and that will return 'SP1' as a result, but how then to return the fully qualified 'MYBIGTABLE' AND 'MYSMALLTABLE' as rows?


texts: query returns the same value multiple times

texts:
I have following command :

select *
from Books
JOIN Keys ON Books.Bnumber = Keys.Bnumber
where (
    Books.Author1 like '%,%' and
    Books.Author1 like '%,John%' or (
        Books.Author2 like '%,John%' and
        Books.Author2 like '%,%'
    )
) and
    Name1 like '%' and
    Keys.Keyword like '%'
order by NAME1 ;

The query returns the same record multiple times.

The repeating parts in query (Books.Author1 like '%'...) are needed, for my application requires it. ( I think it should have nothing to do with my problem)

What could cause this?


texts: Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

texts:
we are using a Sybase SQL Anywhere 12 db.

In the db there are indices, which are unique, but shouldn't be unique.

Therefore I search for a quick way to list all tables with unique primary keys.

I tried

SELECT z.name
FROM   sysobjects z
JOIN   sysindexes
ON     (z.id = i.id) WHERE type = 'U'

The result was an error message:

  Anweisung konnte nicht ausgeführt werden.
  Tabellenname 'sysindexes' ist mehrdeutig
  SQLCODE=-852, ODBC 3-Status="42S13"
  Zeile 1, Spalte 1

Roughly translated:

  sysindex table name is ambiguous.

I found on internet the query:

select 'Table name' = object_name(id),
       'column_name' = index_col(object_name(id),indid,1),
       'index_description' = convert(varchar(210),
        case when (status & 16)<>0 then 'clustered' else 'nonclustered' end
        + case when (status & 1)<>0 then ', '
        + 'ignore duplicate keys' else '' end
        + case when (status & 2)<>0 then ', '
        + 'unique' else '' end
        + case when (status & 4)<>0 then ', '
        + 'ignore duplicate rows' else '' end
        + case when (status & 64)<>0 then ', '
        + 'statistics' else case when (status & 32)<>0 then ', '
        + 'hypothetical' else '' end end
        + case when (status & 2048)<>0 then ', '
        + 'primary key' else '' end
        + case when (status & 4096)<>0 then ', '
        + 'unique key' else '' end
        + case when (status & 8388608)<>0 then ', '
        + 'auto create' else '' end
        + case when (status & 16777216)<>0 then ', '
        + 'stats no recompute' else '' end),
        'index_name' = name
from    sysindexes
where   (status & 64) = 0
order by id

Which looked what i wanted. But there was still the same result of ambiguous sysindexes.

What dows ambigious indexes mean in this context? Will/Can this cause any error in future?

As workaround I used sybase central (which by the way opens always on first monitor, not on the one where it was closed - ugly behaviour), and found that a item indices showed what i searched for.

But I still want to know how a programmatically solution looks like.


texts: Excessive RAM consumption in MySQL Cluster

texts:
at this moment I have configured a cluster that has 5 nodes:

2 data nodes
2 SQL nodes
1 node manager

My file.config.ini is the following:

[ndbd default]

NoOfReplicas = 2 # Number of replicas
DataMemory = 256M IndexMemory = 100M
ServerPort = 2202.
MaxNoOfConcurrentTransactions = 429496
MaxNoOfConcurrentOperations = 472496
MaxNoOfLocalOperations = 519745

[ndb_mgmd]

NodeId = 1
HostName = 192.168.10.145 # Hostname or IP address of MGM node
DataDir = / var / lib / mysql-cluster # Directory for MGM node log files

[ndbd]

HostName = 192.168.10.181 # Hostname or IP address
NodeId = 2 # Node ID for this data node
DataDir = / mnt / dataPartition / mysql / data # Directory for this data node's data files

[ndbd]

HostName = 192.168.10.183 # Hostname or IP address
NodeId = 3 # Node ID for this data node
DataDir = / mnt / dataPartition / mysql / data # Directory for this data node's data files

[mysqld]

HostName = 192.168.10.140 # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

[mysqld]

HostName = 192.168.10.184 # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

[mysqld]

IN THE NDB_MGM REPORT, IT SAYS THE FOLLOWING:

ndb_mgm> 2 REPORT MEMORYUSAGE
Node 2: Data usage is 18% (1476 32K pages of total 8192)
Node 2: Index usage is 11% (1461 8K pages of total 12832)
ndb_mgm> 3 REPORT MEMORYUSAGE
Node 3: Data usage is 18% (1480 32K pages of total 8192)
Node 3: Index usage is 11% (1462 8K pages of total 12832)

Each data node has 2gb of ram, it is assumed that with this configuration should not consume more than 500 mb of ram the ndbd process, but I am consuming 1.89 gb of ram per node, I am using disk storage for the fields that they are not indexed

What is wrong or what is wrong?


texts:
I am facing the myisam tables get Corrupted frequently but I don't know exact root-cause for this problem. However I will do Repair/MyIsamchk --auto-repair whenever the table gets corrupted. Please see the below actions,

mysql> check table tableName;
+--------------------+-------+----------+----------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                 |
+--------------------+-------+----------+----------------------------------------------------------+
| database.tableName | check | warning  | 2 clients are using or haven't closed the table properly |
| database.tableName | check | warning  | Size of datafile is: 13540475       Should be: 13540450  |
| database.tableName | check | error    | Found 541619 keys of 541618                              |
| database.tableName | check | error    | Corrupt                                                  |
+--------------------+-------+----------+----------------------------------------------------------+
4 rows in set (3.83 sec)

mysql> repair table tableName;
+--------------------+--------+----------+----------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                     |
+--------------------+--------+----------+----------------------------------------------+
| database.tableName | repair | warning  | Number of rows changed from 541618 to 541619 |
| database.tableName | repair | status   | OK                                           |
+--------------------+--------+----------+----------------------------------------------+
2 rows in set (15.28 sec)

Server configurations are,

CentOS - 64 Bit (Linux - ext3)
RAM - 2GB
Hard disk - 79GB (Please see the disk free space details below)

Shell# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       79G   57G   18G  76% /
tmpfs          1000M     0 1000M   0% /dev/shm

There will be free space is available on the Hard disk but why the table gets corrupted? and also kindly provide the solution to prevent this problem?

Thanks in advance.


texts: MySQL Semi-synchronous replication with Multi-Master

texts:
Is it possible to use semi-synchronous replication with a Multi-Master setup?

I've tried to follow this guide to setup a semi-synchronous replication for a master-slave setup: https://avdeo.com/2015/02/02/semi-synchronous-replication-in-mysql/

But I'm not sure how to implement this on a Multi-Master setup.

There are two plugins: one for the master and one for the slave. Since a Multi-Master act as a Master and Slave, does that mean I have to install both plugins on all servers?

I'm using MySQL 5.7


texts: When to use NULL and when to use an empty string?

texts:
I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general:

Is there a logical scenario in which it would be useful to distinguish an empty string from NULL?
What would be the physical storage implications for storing an empty string as...

NULL?
Empty String?
Another field?
Any other way?


texts: MySQL Not Recovered After Running Out of Space

texts:
I've got a MySQL 5.7.26 running on Ubuntu 16.04. I'm running an app that's loading a few GB data into the database, which was running fine until I let the root volume (with MySQL on) run out of space - I let a ton of application log files build up - after which MySQL started timing out transactions. This is just a dev server, so is pretty small and not closely monitored.

Anyway, I've since deleted all the log files and restarted MySQL and my app, but it's not back to its old self. The transactions aren't being timed out, but they are taking ages (used to be sub-second, now up to 45s). I'm guessing that running out of disk space has put MySQL in an odd state, meaning it still thinks it's not got space to grow data or log files.

So is there something I can to do to "recover" MySQL? To tell it that it can start expanding files again? Thanks!


texts: How to removing or reduce the size of redo logs in MySQL cluster

texts: Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we disable Redo logging in MySQL cluster.


texts: Why some of mysql system variables are readonly

texts:
There are almost more or less 277 mysql server system variables .
In these variables some of the variables are dynamic and few are not.

I don't under stand what is the reason behind read only variables .  why few of mysql system variables are read only. what will happen if they make those variable dynamic.

Is there any deep reason for the read only variables?

For now we can consider :

innodb_buffer_pool_size
innodb_checksums
datetime_format
log-bin

and there are many other variable we can find at
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Of course, I know we can't change variable like version. But many other variables like log-bin are not dynamic. For me, it does not make sense if general_log and log_slow_queries are dynamic. Why is log-bin not dynamic? like wise there are many other variables.


texts: How to determine if an Index is required or necessary

texts:
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that need creating.

Edit: The Indexes described above take information from the DMV's that tell you what the database engine would use for indexes if they were available and the scripts take the Top x recommendations (by seeks, user impact etc.) and put these in a table.

(Edit above partially taken from Larry Coleman's answer below in order to clarify what the scripts are doing)

As I am new to database admin, and having had a quick search around the net, I am reluctant to take the plunge and blindly add the recommended indexes. However, not being experienced in  the field, I am looking for some advice on how to determine whether the recommendations are necessary or not.

Do I need to run the SQL Profiler, or is it better to examine the code that queries the tables? And do you have any other advice?


texts:
why am I encountering this error when executing impdp command?

My command looks like this:

impdp directory=dp_dir dumpfile=testdb.dmp logfile=testdb.log

Error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation


texts: Understanding performance of highly unique WHERE clauses

texts:
I've been struggling to understand how to deal with a specific type of performance issue that shows up very often in this scenario: when you are wanting to apply multiple filters in a query, but you know that first filter will return a very small number of rows from a very large table.

For example, we have a 3rd-party heterogeneous table with 10M+ rows where the columns mean different things based on the "object type" in TYPEID.  Here is an example query:

SELECT ID, NAME, INT109 FROM DATA WHERE TYPEID = 8301514 AND INT109 = 1

In this query, there are no covering indexes for the two filters, but there is an index on the 'TYPEID' column.  What is perplexing is that even though there are only about 500 rows out of the 10M in the table with TYPEID = 8301514, this query sometimes takes many seconds to run.

If I simply remove the INT109 = 1 filter at the end, the query runs almost instantly:

SELECT ID, NAME, INT109 FROM DATA WHERE TYPEID = 8301514

It makes no sense to me that having fewer filters would make a query run much faster.  Also the behavior seems to be inconsistent - the first query can run really fast too if it has been run multiple times, like something is being cached. It's difficult to do reliable experiments (this is in SQL Azure).  Is this normal behavior?  Is this something that can be caused by a bad execution plan (even though I'm not using parameters) or statistics that are out of date?


texts: `make install` on a module, I get `pgxs.mk:114: recipe for target 'install' failed`

texts:
I'm trying to install a PostgreSQL module (pg-bignum). When I try make install, I get the following at the bottom of the log

/usr/bin/install: will not overwrite just-created '/usr/share/postgresql/9.5/extension/bignum--0.8.0.sql' with './/sql/bignum--0.8.0.sql'
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk:114: recipe for
target 'install' failed
make: *** [install] Error 1

Currently using PostgreSQL-9.5 and Ubuntu.


texts: Does MSSQL log shipping automatically remove the backup files from both servers?

texts:
Does Microsoft SQL Server's log shipping feature remove the backup files automatically? I am concerned that the secondary server's hard drive will reach capacity unless the previously restored files get removed.

I have not been able to find a reliable answer to this anywhere.


texts: Unable to insert a data in a table in mysql


texts:
I have developed an application in Java that is registered on Google App Engine. I need to store some data on data store. But, I am completely unaware about its implementation.

Actually, I have implemented OAuth, so I want to store data of particular user who has logged in. Is it possible to separate out data stores for different users ?

Also, there is some quota limitation. How much data can we store for whole application ?


texts: Primary key addition for a table which contatins no data is too slow

texts:
We are using mysql. We are running 10 alter queries which contains 3 Primary key deletion and 4 column addition and 3 primary key addition. And this alter query is on three tables which don't contain any data. To run this query system took 15s. Usually system wont take this much for alter query. But for a table which contains no data I am not sure why it is taking this much of time.

I am not sure whether it is because of primary key addition and deletion or any mysql configuration is wrong or because of the machine.

While this migration alone system is slow. After and before the migration is system is behaving as normal.

Can anyone help me on this one?

Below are the queries. I was running three sets like this.

ALTER TABLE Table1 DROP PRIMARY KEY;

ALTER TABLE Table1 ADD Column3 INT(10) DEFAULT ‘1';

ALTER TABLE Table1 ADD PRIMARY KEY Table_PK(Column1, Column2 , Column3 );


texts: Loop through slices of 2-dimensional array in plpgsql function?

texts:
How to loop through an array of arrays in a function and access its elements?

Below is the the function (this is only the relevant part).

create  or replace function public.whatwhatwhy(
        _fields character varying []
    ) returns void as
$func$
declare strSQL text := '';
declare _field text := '';
begin
    foreach _field in array _fields[:][1:2]
        loop
            strSQL :=  'fieldname:' || _field[1] || ' stats: ' || _field[2] ;
            raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL;
        end loop;
end
$func$ language plpgsql;

I want to call the function with:

select public.whatwhatwhy(ARRAY[
['total_pop', 'sum'],
['total_male_pop','sum'],
['median_female_age','median']
])

Question
How do I access e.g. total_pop and sum in my _field? I assumed the _field variable holds another array however I get an error:

  SQL Error [42804]: ERROR: cannot subscript type text because it is not an array
  Where: SQL statement "SELECT 'fieldname:' || _field[1] || ' stats: ' || _field[2]"

Note

I tried this statement, hoping that this solves the problem, but it seems to be identical to the statement above.

select public.whatwhatwhy(ARRAY[
ARRAY['total_pop', 'sum'],
ARRAY['total_male_pop','sum'],
ARRAY['median_female_age','median']
])


texts: In AWS Aurora's performance insights. What qualifies as "Other" and how do I investigate it?

texts:
I have a disproportionate amount of queries that are marked as "Other" in performance insights with no additional details. AWS doesn't seem to provide any details about what this means, and asking on the forum doesn't seem to work.

Is there a way to get details about what falls into Other?


texts: Monitoring slow queries on large database (Percona 5.6)

texts:
I am looking for a solution to monitor very large production database (many replicas, over 200 tables, some of tables have 10-50 GB of data) with a lot of traffic (a wide set of query complexity).
The main goal is to keep an eye on heavy queries after application code change. We have experienced that new code introduced non-optimal sql queries.
The database dump is very huge so we use a sample on staging environments only. It is almost impossible to detect performance issue on staging in this case.
I thought about using something like New Relic but for such  big infrastructure costs are too high and I am not sure it affects performance in a negative way.
On the other I can use MySQL slow log. I enabled it on some replicas with a log rate limit. After one month I do not see I/O speed impact but I am not sure about enabling it for all replicas and the master database.
I addition I found this article http://blog.symedia.pl/2016/10/performance-impact-general-slow-query-log.html and it says something different that I read in the past.
What would you do to monitor large database?


texts: IS_SRVROLEMEMBER returning NULL for valid non windows user

texts:
I have a service that executes a stored procedure to query the db for results. The stored procedure needs to use impersonation on the user who is logged in the client (since the service has its own separate credentials to connect to the db). The name of the user logged in the client is passed as a parameter (@loginUser) to the stored procedure.

If the @loginUser has sysadmin rights in the sql server, the procedure should not do any impersonation and simply proceed to query the table. The impersonation should only be used for users without sysadmin permissions.

To do that, I am using the following statement in the procedure.

DECLARE @HasSysadminRole BIT

SELECT @HasSysadminRole = IS_SRVROLEMEMBER('sysadmin', @loginUser)

IF @HasSysAdminRole = 0
  EXECUTE AS USER = @loginUser
...
...
...

IF @HasSysAdminRole = 0
  REVERT

The procedure is created on the db using the sa login.

Now, if I execute the procedure with a user who doesn't have sysadmin permissions, I get NULL as the value of  IS_SRVROLEMEMBER('sysadmin', @loginUser) even though the @loginUser is a valid user in the db and server.

If I run the same IS_SRVROLEMEMBER('sysadmin', @loginUser) statement in a query window in Sql mgmt studio, logged in as sa, I get the value 0.

I do not know why the sql server is behaving differently in these 2 circumstances.


texts: How to restore IBM DB2 database from AIX to Windows?

texts:
I had made backup from remote IBM DB2 server in AIX using this command:

./db2 backup database DBEMP to /home/dbemp/backup

Then I grab the backup file using FTP Client from my Windows.

I run this command to restore the database in my local machine:

./db2 restore database DBEMP from "C:\backup" TAKEN AT 20130521002219

But I got this error message:

  SQL2570N  An attempt to restore on target OS "NT-64" from a backup
  created on source OS "AIX-64" failed due to the incompatibility of
  operating systems or an incorrect specification of the restore
  command.  Reason-code: "1".

So what is the solution for this?


texts: Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

texts:
I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition.

My problem is with SSMS 2012 which can connect to distant servers but not the local 2012 instance; I can however connect to this instance from SSMS 2008.

The error message I get when trying to login is

  Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452)

I must point out that I don't have the necessary privileges to access SQL Server Configuration Manager (blocked by group policy).

Any help would be appreciated.


texts: SQL Server sp_describe_first_result_set: find user defined types

texts:
I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for UDTs seems to be missing from the procedure result. Given the sample code:
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @test TABLE (
        a BIGINT
        ,b udtTest
        ,c NVARCHAR(100)
        ,d BIT
    )

    INSERT INTO @test (
        a, b, c, d
    ) VALUES (
        1, 'udtTest', 'nvarchar', 0
    )

    select *
    from @test
END
GO
EXEC [dbo].[sp_describe_first_result_set] @tsql = N'test';
GO

I'm seeing the following (db<>fiddle):

name
system_type_name
user_type_name

a
bigint
NULL

b
nvarchar(123)
NULL

c
nvarchar(100)
NULL

d
bit
NULL

Why is udtTest missing from my data for column name b?


texts: Deferred Name Resolution for Linked Servers

texts:
I need to be able to create stored procedures that point to a linked server where the database does not yet exist on the linked server.

The thought process is that the above mentioned stored procedures are a part of a larger Database Creation script. My company wants to run this database creation script on the live servers to create new databases, make sure everything is OK, add all data, and then migrate select (daily snapshots) data to a Report server (the linked server).

I have created Synonyms that will be in the database creation script that point to a linked server called EDI_Report_Server. For example:

IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE object_id =
               OBJECT_ID(N'[dbo].[EDI_SnapshotLog]') )
BEGIN
       SET @SynonymCreateSQL = N'CREATE SYNONYM [dbo].[EDI_SnapshotLog]
                               FOR [EDI_Report_Server].' + DB_NAME() +
                               '.[Snapshot].[SnapshotLog]'
       EXEC dbo.sp_executesql @SynonymCreateSQL
END

And in this database creation script I will create a stored procedure that has code similar to this:

INSERT INTO EDI_SnapshotLog (
         [SnapshotLoadStartTime]
        ,[SnapshotLoadEndTime]
        ,[SnapshotLoadResult]
        ,[SnapshotValidationErrors]
        )
...
...

Trying to run it gives the error:

  The OLE DB provider "SQLNCLI10" for linked server "EDI_Report_Server" does not contain the table """."Snapshot"."SnapshotLog"".

Must I create the database and tables on the linked server first, or is there a way to defer the name resolution on linked servers?

Thank you for your time


texts:
I just found a database, and I have no Idea what to do. I can't drop, update, or anything.

The database is in this state:

How can i turn it off? Because, it's already off !

I tried to restore database ...set online.


texts: Does using secondary replica for read operations still required license?

texts:
I wanted to move some of the reporting workload to the secondary replica but the following things stopped me:

no writes can be performed (including creating temporary tables);
a license is required;

I guess I might get it wrong... I've checked the docs again and it is said that:

  Though you cannot write data to secondary databases, you can write to
  read-write databases on the server instance that hosts the secondary
  replica, including user databases and system databases such as tempdb.

which basically means I can execute every reporting query.

But I am not able to find any information about the licensing in the docs. Is the licensing requirement off, now?


texts: Proc For SSRS Report shows a high number of executions ExecutionLog shows low Execution count for Report

texts:
So, I am using SolarWinds DPA tool to monitor DB activity and obv do things based on that information.

I found a stored procedure that is being used for a SSRS report that has a high number of executions (in the millions) where as the ExecutionLog from the ReportServer shows that the report itslef is utilized maybe 30 or 40 times a day.

Now, I am not that knowledgeable on SSRS, so maybe its a thing I am not aware of?

Can anyone provide any information that might be useful in my endeavor to figure out why there is such a high execution count? Most of googling turns up unrelated results, so maybe I dont know what exactly i should be googling?

In the mean time, I am going to run a trace on the server and see what that shows me.

Thanks in advance.


texts: Logging multiple error message in the TRY...CATCH when doing BACKUP DATABASE

texts:
When I try below:

backup database not_existing_db to disk = 'E:\Backup\not_existing_db.bak'

SSMS shows two error messages:

Msg 911, Level 16, State 11, Line 22
Database 'not_existing_db' does not exist. Make sure that the name is entered correctly.

Msg 3013, Level 16, State 1, Line 22
BACKUP DATABASE is terminating abnormally.

When I try to encapsulate it into TRY...CATCH block like below:

begin try
    backup database not_existing_db to disk = 'E:\Backup\not_existing_db.bak'
end try
begin catch
    select ERROR_MESSAGE()
end catch

error_message() only shows second error which is not informative at all

I want to find a way to log both messages, so most informative error message (first) could be send by email to DBA

Please help to find out how to do that


texts: MySQL query hanging on copying to tmp table

texts:
I'm stuck with a query hanging on 'copying to tmp table' for about 25 seconds according to the profiling.

The query is used for forecasting the number of results after filtering by job_type / job_category. So what I want is a list containing all job_types with the number of profiles matching this category.

Some explaination of the used tables:

jb_select_boxes    | containing 191 rows, of which 40 job_types are used
jb_users           | containing around 175k rows
jb_seeker_profiles | containing a row for every user, so also about 175k

The query being used to acchieve this (Works, but takes about 25 seconds)

SELECT
    sb.name_nl, sb.name, count(*) as nr
FROM
    jb_select_boxes as sb
LEFT JOIN
    jb_seeker_profiles as sp ON job_category1 = sb.name OR job_category2 = sb.name
JOIN
    jb_users as u ON u.id = sp.user_id
LEFT JOIN
    jb_seeker_profiles_edu as spe ON u.id = spe.user_id
LEFT JOIN
    jb_addresses as a ON u.id = a.user_id
LEFT JOIN
    jb_seeker_profiles_exp AS exp ON u.id = exp.user_id
LEFT JOIN
    jb_resumes AS jbr ON u.id = jbr.user_id AND jbr.type='html2pdf' AND jbr.resume_privacy='378'
WHERE
    sb.group_name = 'job_types' AND u.status = 'seeker' AND u.completed = 1
GROUP BY
    sb.name_nl
ORDER BY
    nr DESC

Explain result of the query:

I already tried to play with the max_heap_table_size and tmp_table_size variables but without positive effects. Values are now at 256M.


texts: How to install SQL Server Management Console in 2008 Express Version?

texts:
I started a dozen attempts to install the SQL Server Management Studio for the 2008 Express Version of SQL Server.

What i use:
Windows Vista 2007 SP 2

What i did:
a) I downloaded the install file SQLManagementStudio_x86_DEU.exe from Microsoft's Download Center

b) I started the setup and chose "Installation"/"Adding Feature"

c) The feature selection appears, but I can chose only from SQL Client Connectivity SDK

d) This is wrong I guess; but even if I click next I get an error message like "Please chose a feature first"

What can I do?

Thanks for any advice!!!


texts: How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?

texts:
The below is a valid TSQL statement. But I want to understand how SQL Server processes it.
DELETE A
FROM table1 WITH (NOLOCK)
WHERE
    EXISTS
    (
    SELECT 1
    FROM
        table2 B WITH (NOLOCK)
    WHERE
        B.id = A.id
    )

Because the output of the subquery will be a list of 1s. How does SQL Server know which rows to delete?


texts: How to Implement Different Object Types as Similar Tables?

texts:
I have a database I am attempting to design. I am trying to determine the best approach to declare similar, but different types of objects, each with their own sets of properties (perhaps some properties identical between objects).

Let's say, for example, I am building a mall web app. I want to set up a facility for shop owners to register their stores, type of store and some specific information for that store. The problem I am faced with is the best approach to model different shop types and how to incorporate a model for data storage based on the type of shop.

To further extend the example with some concrete data (let's just pretend this information is all necessary for storage), what would be the best approach to model something like this:

Thai Fooooood
Shop Type: FoodShop
Most Popular Item: Pad Thai
Visitors Last Week: 368

Anti-Static Wristband Repair
Shop Type: RepairShop
Current Computer Repairs: 26
Repair Length Estimate (in business hours): 4
Visitors Last Week: 182

Macroputers
Shop Type: RetailShop
Next Promotion Date: January 2, 2015
Current Week Sales Estimates: 122418
Visitors Last Week: 411

Assuming many other instances of these shop types exist, with each type storing the same data. Does it make more sense to set up a table for each shop type, to use a single table to represent all shop types (using generic data), or is there a better approach?


texts: Which graph database for search engine and gis?

texts:
I just learned there are "graph databases" out there, which are more suited for two applications "search engine" and especially "gis".

To avoid discussions whether a relational DB is better suited and to make clear what kind of Database is needed, here some more details what I want to use it for:

Application (Search Engine): Handling search engine data in a relational DB is slow because you'll have big tables with enormous indeces. Imagine just a table like 'links' which is just from_pageid INT, to_pageid INT with PRIMARY KEY(from_pageid, to_pageid), INDEX (from_pageid) and INDEX (to_pageid); which would have some 1'000'000 Entries. When using a graph database there are just all "from" and all "to" links for each nodes, with or without index, which are right there without looking up a huge index.
Application (GIS): If you have a map, which consists of nodes (with geographical positions) and links between them (=ways, roads, autoroutes...) you'll have a big "links" table to ask for which links (road, ways,...) can be used at node x. Same problem as for the search engine application.

Ok, now some more specialties:

It should be a free software (at no cost). Reason: I want to use it for research/educational purposes (and I currently have to finance this my own). AND: I possibly want to let it become a commercial product. Therefore a "free for non-commercial use"-solution should be at least effordable, if switching to commercial application...
I would like to use PHP, C/C++ for accessing the DB (preferably both should be possible). If also Java/Android is supported, it would even better.
Operating System: Linux!; I would like to avoid a Java based product, as the javaVMs tends to eat up all RAM (only have very limited server resources) and don't free any - even if they could.

What DBs would you suggest to me?


texts: Drop a replicated subscription table that has been orphaned after restore migration

texts:
We restored a database on a new server for a migration.

When attempting to set a subscription to a transnational publication, we get the error:

  Can't drop the table because it is set for replication.

However, it is not being replicated to.

I have already set several push subscriptions up from this server that go to another so I don't want to completely wipe out all replication and rebuild everything.

I was wondering if there is a place that I could delete the piece of information that makes the article think it is still replicated to, so that I can add it back to a subscription

Date        1/29/2018 3:42:13 PM
Log     Job History

Step ID     1
Server      Server1Pub
Job Name        RepJobName
Step Name       Run agent.
Duration        00:00:05
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
2018-01-29 21:42:13.174 Copyright (c) 2016 Microsoft Corporation
2018-01-29 21:42:13.174 Microsoft SQL Server Replication Agent: distrib
2018-01-29 21:42:13.174
2018-01-29 21:42:13.174 The timestamps prepended to the output lines are expressed in terms of UTC time.
2018-01-29 21:42:13.174 User-specified agent parameter values:
            -Publisher Server1Pub
            -PublisherDB DBPub
            -Publication PubName
            -Distributor Server1Pub
            -SubscriptionType 1
            -Subscriber Server2Sub
            -SubscriberSecurityMode 1
            -SubscriberDB db1
            -XJOBID 0x466A0D3EF212C442B2B0E6521144ED71
            -XJOBNAME ReplicationJobName
            -XSTEPID 1
            -XSUBSYSTEM Distribution
            -XSERVER Server2Sub
            -XCMDLINE 0
            -XCancelEventHandle 0000000000006F44
            -XParentProcessHandle 000000000000592C
2018-01-29 21:42:13.174 Startup Delay: 2294 (msecs)
2018-01-29 21:42:15.474 Connecting to Subscriber 'Server2Sub'
2018-01-29 21:42:15.509 Connecting to Distributor 'Server1Pub'
2018-01-29 21:42:16.279 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 1
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors
            -transactionsperhistory 100
2018-01-29 21:42:16.864 Initializing
2018-01-29 21:42:17.239 Snapshot will be applied from the alternate folder '\\192.168.5.124\Replications\unc\Folder'
2018-01-29 21:42:17.914 Agent message code 3724. Cannot drop the table 'dbo.table' because it is being used for replication.
2018-01-29 21:42:18.144 Category:COMMAND
Source:  Failed Command
Number:
Message: drop Table [dbo].[table]

2018-01-29 21:42:18.194 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  3724
Message: Cannot drop the table 'dbo.table' because it is being used for replication.


texts: How to install Oracle sqlplus, rman utilities on local Windows workstation?

texts:
I am the administrator for our organization's Oracle database.  I would like to install sqlplus and rman client utilities on my local workstation so I can administer the database without having to remote to it.  I have Googled for instructions on how to do this but cannot find any helpful information.

How can I install sqlplus, rman utilities on my local workstation?


texts: Teradata how to create a sysdba super user

texts:
I am relatively new to Teradata.  I was told by my Teradata consultant to create a super user or a role for DBAs to use, in stead of using DBC. I tried the following syntax, but kept getting errors:

*** Failure 5613 GRANT OPTION cannot be granted to a role. Statement# 1, Info =0

  grant all on "ZZ_TEST" to "ROLE_DBA" with grant option;

Where role_dba is a DBA role, zz_test is a database, and I am using DBC to execute this command.

Any suggestion where the syntax is wrong?


texts: MySQL After Update Trigger apparently not running

texts:
I have the following After Update trigger on a MySQL table:

CREATE
    DEFINER = 'root'@'localhost'
TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD
    AFTER UPDATE
    ON phpbb_rathermenacing.phpbb_bbdkp_memberlist
    FOR EACH ROW
BEGIN
    IF NEW.member_confirmed = 1 AND
        NEW.member_rank_id <> OLD.member_rank_id AND
        ((select min(m.member_rank_id)
            from phpbb_bbdkp_memberlist m
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                  m.member_guild_id = NEW.member_guild_id AND
                  m.member_id != NEW.member_id) >= new.member_rank_id OR
        (select count(m.member_rank_id)
           from phpbb_bbdkp_memberlist m
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND
                m.member_guild_id = NEW.member_guild_id AND
                m.member_id != NEW.member_id) = 0)
        THEN
            BEGIN
                IF (SELECT count(*)
                      FROM phpbb_user_group ug
                     WHERE ug.group_id =
                                    (SELECT mr.phpbb_group_id
                                       FROM phpbb_bbdkp_member_ranks mr
                                      WHERE mr.guild_id = OLD.member_guild_id AND
                                            mr.rank_id = OLD.member_rank_id) AND
                    ug.group_leader = 0 ) > 0
                THEN
                    UPDATE phpbb_user_group ug
                        SET ug.group_id = (SELECT mr.phpbb_group_id
                                             FROM phpbb_bbdkp_member_ranks mr
                                            WHERE mr.guild_id = NEW.member_guild_id AND
                                                  mr.rank_id = NEW.member_rank_id),
                            ug.user_pending = 0
                        WHERE ug.user_id = NEW.phpbb_user_id AND
                              ug.group_id = (SELECT mr.phpbb_group_id
                                               FROM phpbb_bbdkp_member_ranks mr
                                              WHERE mr.guild_id = OLD.member_guild_id AND
                                                    mr.rank_id = OLD.member_rank_id);
                ELSE
                    IF(SELECT count(*)
                         FROM phpbb_user_group ug
                        WHERE ug.group_id = (SELECT mr.phpbb_group_id
                                               FROM phpbb_bbdkp_member_ranks mr
                                              WHERE mr.guild_id = NEW.member_guild_id AND
                                                    mr.rank_id = NEW.member_rank_id)) = 0
                    THEN
                        INSERT INTO phpbb_user_group (group_id, user_id, group_leader, user_pending)
                               VALUES ((SELECT mr.phpbb_group_id
                                          FROM phpbb_bbdkp_member_ranks mr
                                         WHERE mr.guild_id = NEW.member_guild_id AND
                                               mr.rank_id = NEW.member_rank_id),
                                      m.phpbb_user_id, 0, 0);
                    END IF;
                END IF;
            END;
    END IF;
END

Basically, the trigger is checking to see if one type of group that the user is associated with has been changed, and in the event that it has been changed, it makes an alteration to set a reference for another type of group.

The problem I'm having is that while the trigger saves perfectly fine, nothing seems to happen when I try doing updates.  I can change the rank_id for a memberlist row, but it just updates the row.  I've even tried debugging with dbForge Studio and while it will stop on the UPDATE query I use to actually make the update, stepping in to the UPDATE method does not appear to hit the trigger.

What possibilities could be causing the debugging break points to never be hit and no apparent behavior from the trigger to be seen?


texts:
I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out that IF() is a MySQL-ism. So, I looked up the conditionals for PostgreSQL and CASE seemed to be the best tool.

However, I noticed that CASE ends with END in PostgreSQL, while in MySQL it ends with END CASE. Will this destroy my hopes for database agnosticism? Can I write an agnostic query with a conditional?


texts:
Can't understand a strange behavior of GROUP BY clause:
SELECT id, SUM(num) as sum
FROM (
    SELECT 1 AS id, 2 AS num UNION
    SELECT 1, 3) AS a
GROUP BY id

results in:
id  | sum |
----|-----|
1   | 5   |

but
SELECT id, SUM(num) as sum
FROM (
    SELECT 1 AS id, 2 AS num UNION
    SELECT 1, 2 /*!*/) AS a
GROUP BY id

results in:
id  | sum |
----|-----|
1   | 2   |


texts: What is the best way to create tables for email campaigns?

texts:
I have table email_sent with fields:

id
user_id
template_name
sent_at
opened clicks

And now I need to save data which was send for each email. I have several campaigns with differnt data. For example, campaign wich recommend call to english teacher, if user call to teacher until expire date service will return 10% of charge. So for each email I need to save teacher_id and expire date. I have serveral ways.

First to create table call_discount with fields: email_sent_id, teacher_id, expire.

Second to create table email_sent_data with fields: id, fieldname, value and table email_sent_to_data with fields: email_sent_data_id, email_sent_data. But I will have huge amount of data and it will be hard to make right indexies.

Third to create table email_sent_data with fields: id, teacher_id, expire. Add column email_sent_data_id to table email_sent. And if I have new campaing I will add new fields to email_sent_data.

I have postgresql 9.1. So I can't work with JSON.


texts:
i am working on a POC where i need to implement same like SSIS for sql to hadoop.
out DW are in SQL Server and we want to create a HBase at central location where all the data from diff DW will be posted. for the first time load i can do it using "sqoop import" command. but after then for changed data how to do incremental of sqoop with HBase?


texts: what is the formal statement terminator with db2 sql

texts:
I have added / character as statement terminator all in my db2 statements. I can run sql file in data studio client without any issue. But when I run the sql file in db2 command prompt by issuing db2 -tsvf db2.sql command I got

DB21007E End of file reached while reading the command.

error. I could resolve this issue by changing statement terminator character from / to ;.
My question is should I keep statement terminator character as / or ;? If I keep /, is there any way to run sql file from command line also?


texts: INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working

texts:
I'm trying to install semi synchronous replication in my mysql 5.5 and trying to execute the below command:

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so'

but this command is throwing the below error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
''rpl_semi_sync_master' SONAME 'semisync_master.so'' at line 1

Please help regarding this. We are using Win7 Professional SP1 32-bit OS.

Thank You


texts: The time is wrong in MySQL "data/*.err" file

texts: I use "select now();" in MySQL client, the return time is right, is my system time. But the time in the "data/g1.err" is wrong, the "g1" is my computer name. The time's minute is right, so I think this is the problem of timezone. How can I correct the time in the error log time?


texts: making a where clause optional in a sql statement

texts:
Here is my current query:

`SELECT `performers`.`hash`, `performers`.`alias`,
  `performers`.`date_updated`, `performers`.`status`,
  IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
  `images`.`image_hash_file`
FROM `performers`
LEFT JOIN `images` ON `images`.`asset_id` = `performers`.`id`
WHERE (`images`.`asset_type` = 'performer')
ORDER BY `alias` ASC
LIMIT 12`

In it, there is a WHERE clause WHERE (images.asset_type = performer)

I'd like it to be optional, such that if there where clause doesn't fit it still shows the records from the performers table that do not have a join to the fulfilling images records.


texts: How to change MongoDB slowOpThresholdMs in mongo shell

texts: I need to decrease the slowms trash hold to 50, can't find the way of doing that without restarting the mongod process.


texts: MySQL 5.6 to 5.7 In-Place Upgrade or Fresh Install?

texts:
I'm preparing to migrate a production database server on AWS RDS to 5.7 from version 5.6.

I'm not sure that I trust the automatic upgrade service. Obviously, I would make a backup prior to doing anything, but is there any reason one should choose to just start a new instance and manually export / import the data (and privileges, of course)?


texts:
I've installed the azure cosmos db in my local machine but is not loading the explorer pages as shown below:

Explicit error:

Failed to get authorization headers for offers: {"readyState":4,"responseText":"{\"code\":\"ServiceUnavailable\",\"message\":\"Service is currently unavailable.\r\nActivityId: d728958b-c0b4-4b32-a131-bba1dc7a8ba0, \r\nResponseTime: 2018-06-05T17:22:33.8740685Z, StoreReadResult:


texts: MySQL Server 5.6 only runs when making swapfile

texts:
I am new to MySQL server. I have set up a dedicated MySQL server. I am aiming to install MySQL server version 5.6 and since version 5.6 is not available on the Ubuntu repository, I have to upgrade it myself. Unfortunately, upgrading the MySQL server is a pain.

Everything went smoothly up until starting the MySQL by using the command:

sudo service mysql.server start

It will issue an error:

  The server quit without updating PID file (/var/run/mysqld/mysqld.pid)

I've searched far and wide on what's causing this error. Unfortunately, I have found none. Not until a colleague of mine suggested making a swapfile. And thankfully enough it worked! Right now the MySQL server is running smoothly.

I was just wondering what caused the error and what are the drawbacks in this kind of setup in the long run?


texts: How to make sqlplus output appear in one line?

texts:
I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read.

What I'd rather like is either a horizontal scroll bar to appear or somehow send the output to less

I run following statements in SQLPlus -

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SPOOL output.txt
SELECT * FROM big_table;

Then in bash I run -

  less output.txt

The output still appears wrapped and unreadable.


texts: How can deleting similar number of rows result in very different log growth?

texts:
I have (in my opinion) very strange behavior of SQL Server log growth. I have a database with approximately 8GB log size. I made transaction log backup and started deleting about 4 million rows. The log file did not grow. Then I tried to delete another 4.9 million from another table - the log file grew to about 26 GB and filled the drive.

I managed to fix the situation, but I was curious - since (as far as I am aware) the log file contains only commands to delete records (in this case), how can there be such a big difference between 2 operations when the number of rows affected is only 20% different?


texts:
My folder mysql/data is huge. I have dropped some databases that have a huge amount of records, removed some logs tables but.. mysql/data size remains unchanged.

What is the best way to compact it? It takes something like 20 Gigabytes on HD and contains about 30 medium or small database. My whole databases folder takes 300 Megabytes.

In addition ibdata1, mysql-bin.000332, mysql-bin.000326, mysql-bin.000206 take about 20 Gigabytes.

PS: I use Windows 7


texts: SQL Server Data Tools - Data Link Properties - Server Name Drop Down Empty

texts:
I'm using VS 2019 with the latest version of SQL Server Integration Services Projects plugin. I have 2 local SQL Server instances installed on the same machine which are up and running (including Browser service). SQL Server is 2017 and 2019 (default and named instance respectively) and their Hide Instance flag is set to No.
When adding Data Link for Data Flow SSIS tasks (Destination or Source Assistant) server drop down not showing my SQL instances like that:

At the same time if I type in SQL Server instance name I can connect just fine. What can be causing my SQL instances not showing up here?


texts:
How to change below query to see results of my_sql (now I see only query, not it results) in SQL Developer?

DECLARE
    my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner = 'O' )
    LOOP
        my_sql := 'select max(DATE) from ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

I want to get one value (in this case - max) from all tables (all tables have these same column).


texts: Shrinking the SYSTEM tablespace in Oracle

texts:
Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table.

We have truncated SYS.AUD$ but the datafile is still very big (~30G).

Resize doesn't work because the file contains used data beyond requested RESIZE value

What should I do here?

Here's our version information:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE  11.2.0.1.0  Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


texts: SQL Server Table Design for Report - Is OLAP Schema Suitable

texts:
A work colleague is developing SQL Server database tables for a reporting project. Basically, the only activity on the table will be populating it from a ETL process (SSIS).

He insists on creating a complex normalized design but I do not think this is necessary as there will be no activity on the table other than INSERT from the ETL, and SELECT for viewing the report data.

From what I can tell this is more suitable to OLAP rather than OLTP. Or since all the data is just output to Excel anyway, one table should be enough to store it? There is no aggregation either. Why the need for multiple tables?

Any suggestions on this? Is a simple table the best way forward or is it better to use normalized design?


texts:
I usually right click on a view or a stored procedure and then choose Script View -> Alter -> In New Editor Window.

Is there a way to shortcut this - either on Keyboard or on direct menu entry?


texts: Query to select distinct name based on several factors

texts:
I have a table with the following data (simplified here):

ID | PolicyName | Setting | State | Value

1 Default DiskUsage Enabled 1
2 Default Memory Enabled 1
3 Default CPU Enabled 0
4 Personal DiskUsage Enabled 0
5 Personal Memory Enabled 1
6 Personal CPU Enabled 0
7 Custom DiskUsage Enabled 1
8 Custom Memory Enabled 0
9 Custom CPU Enabled 1

I need to run a query looking for policy names that have Setting of DiskUsage Enabled and set to 1 along with Memory Enabled and set to 1.  So it should return:

PolicyName

Default

Not sure how I can go about this one.


texts:
I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows.  In the first database restored, some functions are with compilation errors.

The query below informs you that there is a syntax error on line 2, but I can not figure out where the problem is.

Error: Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

Query:

create or replace FUNCTION funRetornaDias (p_inicio date, p_termino date)
         RETURN DAYS_INTERVAL_TABLE PIPELINED IS

    v_date date;
    out_rec DAYS_INTERVAL := DAYS_INTERVAL(NULL,NULL);
    v_ref date;

BEGIN

    if p_inicio > p_termino then
        v_ref := p_inicio;
        v_date := p_termino;
    else
        v_ref := p_termino;
        v_date := p_inicio;
    end if;

    while cast(to_char(v_date, 'YYYYMMDD') as number) <= cast(to_char(v_ref, 'YYYYMMDD') as number) loop
    begin

        out_rec.date_ := v_date;
        out_rec.day_of_week := datepart('DW', v_date);

        PIPE ROW(out_rec);
        v_date := v_date + interval '1' day;

    end;
    end loop;

    RETURN;

END;


texts:
I'm getting the following error:

Uncommitted changes pending for file MYTABLE in library MYLIB.
CPF325E
Cause . . . . . :   Uncommitted changes are pending for file MYTABLE in library MYLIB for commitment definition MYTABLE.
Recovery  . . . :   Do one of the following and try your request again:
-- Complete the commitment control process by doing a commit or rollback for commitment definition MYTABLE.
-- Change the commitment control option and compile the program again.
Technical description . . . . . . . . :   The commitment definition identifier is X'5CC4C6E3C1C3E3C7D9D7'. The job that initiated the change(s) is 954538/QUSER/QZDASOINIT. The logical unit of work identifier is *N. The lock space identifier is *N. The XID is X'5CD5404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040'.

Problem is, 954538/QUSER/QZDASOINIT is not an active job. It doesn't show up under active jobs and running this:
 WRKCMTDFN 954538/QUSER/QZDASOINIT

Gives this:

Job 954538/QUSER/QZDASOINIT not found.

...What is happening? How can a job that was finished be connected to pending changes? How can I rollback those changes?
EDIT: Responding to questions in an Answer:

Where are you seeing the message?

That specific one is when, in System iNavigator, I right-click MYTABLE and select Journaling. The original error that made me find the problem is when C# throws:

iDB2SQLErrorException: SQL0910 Object MYTABLE in MYLIB type *FILE has a pending change.

Likewise, I get a "changes are pending" error when trying to remove a referential constraint from MYTABLE.

Do you have *JOBCTL authority? It may be that the job isn't gone, you just don't have authority to see it.

I don't know, nor do I know how to check. If I run:
WRKCMTDFN QUSER/QZDASOINIT

Then a bunch of jobs show up, but 954538 is not one of them.


texts:
I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait events.
The system consists of 3 processing SQL Servers which do alot of number crunching and calculations and then feed the data into a central cluster server. The processing servers can have up to 6 jobs running each at any one time. These wait stats are for the central cluster which I think is causing a bottlneck. The central cluster server has 16 cores and 64GB RAM. MAXDOP is set to 0.
I guess the CXPACKET is from the multiple parallel queries running however I am not sure what the LATCH_EX wait event is indicating. From what I have read this could be a non-buffer wait?
Can anyone suggest what the cause of these kind of waits stats would be and what course of action I should be taking to investigate the root cause of this performance issue?
The top query results are the total wait stats and the bottom query result is the stats over the 1 hour period


texts: Way to update many (>3000) records with unique data, using a character limited web form

texts:
Okay, as you can probably tell from the last part of the title, this is a non-ideal situation.

I'm using a piece of industry-specific web-interface software that I guarantee no one has ever heard of, and while the functionality it offers to do mass data entry is meager, it does allow me to run SQL queries directly on its underlying MS SQL database through an 8,000 character-limited web form. That's the most direct access I have to the database.

I need to update a particular field in about 3,700 records with unique data for each. As per this answer, I've been doing the following:

UPDATE guest
  SET info4 = CASE guestid
    WHEN 12345 THEN 'BNID: 917546553'
    WHEN 67890 THEN 'BNID: B04695465'
    [etc, etc.]
    ELSE info4
  END

(The info4 field is currently empty for all records.) I can fit about 200 cases into one query before hitting the character limit and having to run a new update with the next chunk of data.

Things I have discovered so far:

Not including the ELSE statement at the end will empty the field on all records that don't fulfill a case.
Before committing, I get "Rows affected: [# of rows in the table]" instead of the number of rows that will actually get new data.

I understand I should be putting a WHERE guestid IN (12345, 67890) at the end to avoid running the update on every row, but that's going to be hellish to avoid running over my character limit with each chunk of update statement.

As it happens, I am currently taking my source data from an Excel spreadsheet, pasting it all into Notepad++, running find/replaces on it to insert the necessary SQL syntax, and then chunking it up as per the character limit.

So, in a nutshell, my question is: have I explored this to the extent of what is possible already? Is there something big and obvious I'm missing, or should I be going about this in a completely different way? Bonus points for fewer characters per case!


texts: What are the names of SQL Server database statuses 24, 1024 and 65544?

texts:
using the following query:

SELECT Name, Status FROM dbo.sysdatabases
WHERE name IN ( 'db_name' )

I get in some cases status = 24, other times 1024 and other times 65544

I see that even when status is 24 I can open and check content of the database from SQL Management Studio but not from a legacy application which checks status to be at least 1024.

when I ask DBAs to fix this issue they run some magic scripts and status goes up to 65544 and all works fine, I would like to have some names for these statuses not just numbers like 24, 1024, 65544.

I have already checked this link: sysdatabases

which contains this table:

  Status bits, some of which can be set by the user with ALTER DATABASE
  (read only, offline, single user, and so on):

  1 = autoclose; set with ALTER DATABASE.

  4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.

  8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.

  16 = torn page detection, set with ALTER DATABASE.

  32 = loading.

  64 = pre recovery.

  128 = recovering.

  256 = not recovered.

  512 = offline; set with ALTER DATABASE.

  1024 = read only; set with ALTER DATABASE.

  2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.

  4096 = single user; set with ALTER DATABASE.

  32768 = emergency mode.

  4194304 = autoshrink , set with ALTER DATABASE.

  1073741824 = cleanly shutdown.

but I don't see 24 and 65544 in there :(


texts: MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos

texts: I am trying to repair one MySQL replication setup. One of the database's table at slave side is deleted and the updates from master side are received but because of missing table these updates are not written to the database. This setup is not repaired since many months and the slave has much to catch up. I observed that Exec_Master_Log_Pos value is greater than Read_Master_Log_Pos which should not the case. Is this some kind of bug? I am guessing that it will be back to actual normal when the slave catches up with the master. Anybody familiar with this situation?


texts: How to tell if database tuning is fruitful (PostgreSQL)

texts:
As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the server options, etc.

But what I'm not happy with is that I don't have good metrics to say whether my work is worthwhile or not.

On one off queries optimization I can tell whether I got the query to run faster or not. I can use logs and pgfouine to detect slow queries.

But is there overall ways to quantify how well the database is running, metrics to keep track of that will tell me if I'm generally improving things or not really making a difference, etc?

I'm looking for metrics captured at the database level, not at the application level.


texts:
I have a query which is not using the timestamp  index and scanning 200 million records. I know that the query will not use the index if there is a function applied on top of it. However is there any other way to re-write the query and it can use the index.

    SELECT
*
FROM `collect_location`
where DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) >= '2016-02-24 15:55:01'
and DATE_ADD(timestamp,INTERVAL  -1*-6 HOUR) <= '2016-02-24 16:15:01'\G

 explain SELECT *
    -> FROM `collect_location` where DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) >= '2016-02-24 15:55:01' and DATE_ADD(timestamp,INTERVAL  -1*-6 HOUR) <= '2016-02-24 16:15:01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: collect_location
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 24381470
        Extra: Using where
1 row in set (0.00 sec)

show create table collect_location\G
*************************** 1. row ***************************
       Table: collect_location
Create Table: CREATE TABLE `collect_location` (
  `device_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `timestamp` datetime(3) NOT NULL,
  `location_provider` varchar(128) DEFAULT NULL,
  `collect_altitude` float DEFAULT '0',
  `session_id` varchar(128) DEFAULT NULL,
  `x_param` float(16,8) DEFAULT '0.00000000',
  `y_param` float(16,8) DEFAULT '0.00000000',
  `testcase_id` varchar(128) DEFAULT '',
  `last_location_time` datetime DEFAULT NULL,
  `location_tags` varchar(50) DEFAULT NULL COMMENT 'tag field',
  PRIMARY KEY (`device_id`,`job_id`,`timestamp`),
  KEY `fk_collect_location_job1` (`job_id`),
  KEY `collection_location.timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


texts: Backup to Azure Blob Failing with Hallengren Framework

texts:
We are running IAAS VMs in Azure using SQL Server 2016 SP1 CU2.  We are backing up to blob storage using a URL, and the errors received are shown below and described in the MS Connect URL provided.  The defect is marked as corrected in the CU that we are running.

  1117(The request could not be performed because of an I/O device error.)

https://connect.microsoft.com/SQLServer/feedback/details/3119350

According to the article, the workaround is to specific MAXTRANSFERSIZE. When I provide this parameter(@MaxTransferSize = 4194304), the job starts failing with the error

  The value for the parameter @MaxTransferSize is not supported.

I dug into the stored procedure to find the “OR” condition (@MaxTransferSize IS NOT NULL AND @URL IS NOT NULL).  The complete line from the stored procedure is shown below.  Was there a reason for this last condition to be added as Microsoft is recommending this use.  I temporarily commented out that condition and the job succeeded.

IF @MaxTransferSize < 65536 OR
   @MaxTransferSize > 4194304 OR
   @MaxTransferSize % 65536 > 0 OR
   (@MaxTransferSize > 1048576 AND @BackupSoftware = 'SQLBACKUP') OR
   (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE') OR
   (@MaxTransferSize IS NOT NULL AND @URL IS NOT NULL)

I’m wondering if this last condition should be removed.


texts: How get info about duration of activity in time from MySQL table?

texts:
I have MySQL table "sessions" with columns bellow. In this table I have information about start and end every session which was iniciated from specific IP. In some cases for technical reasons end time could not be determined so in these cases value is "NULL". Example is bellow. I need get information about duration of activity for each "ip". For example 1.1.1.1 was active for 3 days, 2.2.2.2 for 7 days and etc. Is possible get these information with any SQL query please? Thank you for your help.
Table sessions
| sessions | CREATE TABLE `sessions` (
  `id` char(32) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime DEFAULT NULL,
  `sensor` int(4) NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '',
  `termsize` varchar(7) DEFAULT NULL,
  `client` int(4) DEFAULT NULL,
  `asnid` int(10) DEFAULT NULL,
  `redirect` varchar(5) DEFAULT NULL,
  `ipid` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `starttime` (`starttime`,`sensor`),
  KEY `asnid` (`asnid`),
  KEY `fk_grade_id` (`ipid`),
  KEY `idx_sesip` (`ip`),
  KEY `idx_sescl` (`client`),
  CONSTRAINT `fk_grade_id` FOREIGN KEY (`ipid`) REFERENCES `ipinfo` (`id`),
  CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`asnid`) REFERENCES `asinfo` (`asnid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Data example:
| id           | starttime           | endtime             | sensor | ip              | termsize | client | asnid | redirect | ipid |
+--------------+---------------------+---------------------+--------+-----------------+----------+--------+-------+----------+------+
| 000002cf3795 | 2018-06-25 13:24:35 | 2018-06-25 13:24:38 |      4 | 1.1.1.1         | NULL     |     89 |  4167 | NULL     | NULL |
| 000007057b0e | 2017-08-12 13:49:36 | 2017-08-12 13:49:45 |      2 | 2.2.2.2         | NULL     |     53 |   851 | NULL     | NULL |
| 000012420d7e | 2019-01-08 19:58:18 | 2019-01-08 19:58:23 |      6 | 3.3.3.3         | NULL     |     53 | 41735 | NULL     | NULL |
| 0000198bf2d6 | 2018-03-22 15:55:59 | NULL                |      4 | 1.1.1.1         | NULL     |   NULL | 18671 | NULL     | NULL |
| 000019ec3c02 | 2017-08-16 21:37:31 | 2017-08-16 21:37:36 |      5 | 2.2.2.2         | NULL     |     90 |   169 | NULL     | NULL |

On the server I use mysql Ver 14.14 Distrib 5.5.62, but I have full export of database on my local system, where I have the latest version of MariaDB.


texts:
I am trying to set up an Azure SQL Database inside of an elastic pool. I'm not sure if I did it right.  Here's my dashboard:

And here's the actual database:

What makes me wonder if the database is actually inside the elastic pool is what's circled in red in the above screenshot.

  No elastic pool

Here's the server:

And here's the elastic pool:

Another clue that the DB is not in the pool is that the pool shows no usage.

So, I'm pretty sure I did something wrong. But I can't seem to figure out HOW to put the database inside the pool.

Help!?!